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

While noodling on the behavior of __in I realized that it would return a set of kids whose t-shirts had at least one favorite color:

Child.objects.filter(Q(fave_colors__in=F('tshirt_colors'))).distinct()

Negating (~) the filter query provides the opposite of what I’m looking for, i.e. the set of all kids who do not have exactly their favorite t-shirts:

Child.objects.filter(~Q(fave_colors__in=F('tshirt_colors'))).distinct()

So I could subtract that queryset from all kids to get my answer:

unlucky_kids = Child.objects.filter(
    ~Q(fave_colors__in=F('tshirt_colors'))).distinct()

lucky_kids = Child.objects.all().difference.(unlucky_kids)
Back to Top