Django: querying two ManyToMany fields on the same model

Given the following models:

class Color(models.Model):
    name = models.CharField()

class Child(models.Model):
    fave_colors = models.ManyToManyField(Color)
    tshirt_colors = models.ManyToManyField(Color)

How would I construct a query to find children whose t-shirts are exactly their favorite colors i.e.

lucky_kids = Child.objects.filter(
    fave_colors__exact=tshirt_colors
) # obvious but not valid query

We can count the number of (distinct) fave_colors, and then count the number of fave_colors that also appear in tshirt_colors, and check if the two are the same with:

from django.db.models import Count, F, Q

Child.objects.alias(
    nfav=Count('fave_colors', distinct=True),
    ntshirt=Count('tshirt_colors', distinct=True),
    nfav_tshirt=Count(
        'fave_colors', filter=Q(fave_colors__kid_tshirt=F('pk')), distinct=True
    ),
).filter(nfav=F('nfav_tshirt'), ntshirt=F('nfav'))
Вернуться на верх