查询Django中多个字段的列表返回的结果比预期的多

Question

我根据这里提供的答案编写了以下过滤器：Django filter queryset __in for *every* item in list

Distinct_Alert.objects.filter(entities__in=relevant_entities, alert_type=alert_type).annotate(num_entities=Count('entities')).filter(
        num_entities=len(relevant_entities))

我遇到的问题是，它似乎过滤不当，有时它会匹配子列表，我使用get来获取不同的警报，但我注意到其中一些错误，因为它们返回多个匹配，这是罪魁祸首。你知道吗

[2016-07-27 18:02:23,473: WARNING/Worker-4] [<Entity: DOGE>, <Entity: 8.8.8.8>]
[2016-07-27 18:02:23,474: WARNING/Worker-4] [<Entity: potato>, <Entity: DOGE>, <Entity: 8.8.8.8>]
[2016-07-27 18:02:23,475: WARNING/Worker-4] [<Entity: desktop_potato>, <Entity: DOGE>, <Entity: 8.8.8.8>]

我的实体列表应该只包含

[<Entity: DOGE>, <Entity: 8.8.8.8>] 

但它和另外两个很匹配。任何帮助都将不胜感激

我想出的临时方法是：

for alert in distinct_alert_query.all():
            if alert.entities.all().count() == len(relevant_entities) and all([entity in relevant_entities for entity in alert.entities.all()]):
                distinct_alert = alert
                break

其中distinct\u alert\u query指的是上面提到的非常长的查询。这样做的问题是，如果查询在不同的\u警报上与一组大于相关\u实体的实体匹配，它将中断：（

型号：

class Distinct_Alert(models.Model):
    #alert_type = models.ForeignKey(Alert_Type, on_delete=models.CASCADE) for the sake of this problem and the filter, this isn't really needed. 
    entities = models.ManyToManyField(to='Entity', through='Entity_To_Alert_Map')

class Entity(models.Model):
    label = models.CharField(max_length=700, blank=False)
    #entity_type = models.ForeignKey(Entity_Type_Label) not necessary for this problem
    related_entities = models.ManyToManyField('self')

class Entity_To_Alert_Map(models.Model):
    entity = models.ForeignKey(Entity, on_delete=models.CASCADE)
    distinct_alert = models.ForeignKey(Distinct_Alert, on_delete=models.CASCADE)
    entity_alert_relationship_label = models.ForeignKey(Entity_Alert_Relationship_Label, on_delete=models.CASCADE)

    class Meta:
        unique_together = ('entity', 'distinct_alert', 'entity_alert_relationship_label')

Answer 1

试试这个：

from django.db.models import IntegerField, Case, When, F

Distinct_Alert.objects.filter(
    alert_type=alert_type
).annotate(
    num_entities=Count('entities'),
    num_relevant_entities=Count(
        Case(When(entities__in=relevant_entities, then=1),
             default=None,
             output_field=IntegerField()),
    ),
).filter(
    num_entities=F('num_relevant_entities'),
    num_relevant_entities=len(relevant_entities),
)

您的查询：

Distinct_Alert.objects.filter(
    entities__in=relevant_entities,
    alert_type=alert_type
).annotate(
    num_entities=Count('entities')
).filter(
    num_entities=len(relevant_entities)
)

Order of ^{} and ^{} matters。你知道吗