Django查询多对多子集包含 [英] Django query for many-to-many subset containment

问题描述

是否可以使用多对多字段查询子集或超集包含?

Is there a way to query for subset or superset containment with many-to-many fields?

假设每个人都有一个他们想看的鸟类列表，并且每个鸟舍都有一个鸟类列表.对于给定的Person实例，我如何进行查询以查找哪个Aviaries在此人的列表上有每只鸟?同样，对于给定的Person实例，我如何查找在该人的列表中哪些鸟舍只有 个鸟(但不一定是所有鸟).

Suppose each Person has a list of birds they want to see, and each Aviary houses a list of birds. How can I make a query to find, for a given Person instance, which Aviaries have every bird on the person's list? And similarly, for a given Person instance, how do I find which Aviaries have only birds on the person's list (but not necessarily all of them).

这是我的Django 1.5模型:

Here are my Django 1.5 models:

class Bird(models.Model):
    name = models.CharField(max_length=255, unique=True)

class Aviary(models.Model):
    name = models.CharField(max_length=255, unique=True)
    birds = models.ManyToManyField(Bird)

class Person(models.Model):
    name = models.CharField(max_length=255, unique=True)
    birds_to_see = models.ManyToManyField(Bird)

我知道我将如何找到至少有一个人的鸟类的鸟舍，但是我不知道如何在这里适应它. (例如: 多对多字段的django查询集)

I know how I would find the aviaries that have at least one of a person's birds, but I don't see how I would adapt that here. (See, for instance: django queryset for many-to-many field)

如果有一个查询可以满足我的要求，我也想知道是否/为什么比手动"执行此操作更可取.例如，我可以遍历鸟舍，提取每个鸟舍的鸟列表，然后查看该人的birds_to_see是鸟舍鸟列表的子集还是超集:

If there is a query that does what I want, I am also interested to know if/why it's preferable to doing this more "manually." For example, I could loop over aviaries, extract each aviary's bird list, and see if the person's birds_to_see is a subset or superset of the aviary's bird list:

def find_aviaries(self):
    person_birds = set(self.birds_to_see.all())
    found_aviaries = []
    for aviary in Aviary.objects.all():
        aviary_birds = set(aviary.birds.all())
        if person_birds.issubset(aviary_birds):
            found_aviaries.append(aviary)            
    return found_aviaries

感谢您的帮助！

推荐答案

使用Postgres子查询数组构造，您可以在ID上进行注释，然后进行相应的过滤:

Using a Postgres Subquery Array construct, you can annotate on the ids, and then filter accordingly:

birds = Aviary.birds.through.objects.filter(
    aviary=OuterRef('pk')
).values('bird')
aviaries = Aviary.objects.annotate(
    bird_ids=SubqueryArray(birds)
).filter(bird_ids__contains=target_bird_ids)

您还可以使用__contained_by进行其他操作(或者，如果您只想要任何匹配项，则使用__overlap).

You can also use __contained_by to go the other way (or __overlap if you just want any match).

那么您所需要的就是一个合适的SubqueryArray类:

All you need then is a suitable SubqueryArray class:

class SubqueryArray(django.db.models.expressions.Subquery):
    template = 'ARRAY(%(subquery)s)'
    output_field = ArrayField(base_field=models.CharField())

您可能需要根据其PK字段来调整输出字段.

You may need to adjust the output field on that, depending upon what your PK field is.

这篇关于Django查询多对多子集包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！