查询一个列表到django中的多个字段，返回比预期更多的结果

Question

我根据这里提供的答案编写了以下过滤器： 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_alert_query引用了上面提到的真正长的查询。这方面的问题是，如果查询在Distinct_Alerts上与一组大于relevant_entities的实体进行匹配，它将中断:(

模型：

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)
)

matters。