djangofilterquerying

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, related_name="kid_fave")
    tshirt_colors = models.ManyToManyField(Color, related_name="kid_tshirt")

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

Solution

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