Filter a Django queryset based on specific manytomany relationships

Question

I have models similar to the Django documentation's pizza example:

class Pizza(models.Model):
    name = models.CharField()
    toppings = models.ManyToManyField('Topping')

    def __str__(self):
        return self.name


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

    def __str__(self):
        return self.name

And a few toppings with the expected pizzas:

>>> pepperoni = Topping.objects.create(name='pepperoni')
>>> sausage = Topping.objects.create(name='sausage')
>>> pineapple = Topping.objects.create(name='pineapple')
>>> olives = Topping.objects.create(name='olives')
>>> p1 = Pizza.objects.create(name='Pepperoni')
>>> p1.toppings.add(pepperoni)
>>> p2 = Pizza.objects.create(name='Sausage')
>>> p2.toppings.add(sausage)
>>> p3 = Pizza.objects.create(name='Pepperoni and Sausage')
>>> p3.toppings.add(pepperoni)
>>> p3.toppings.add(sausage)
>>> p4 = Pizza.objects.create(name='Pepperoni and Olives')
>>> p4.toppings.add(pepperoni)
>>> p4.toppings.add(olives)
>>> p5 = Pizza.objects.create(name='Pepperoni and Sausage and Olives')
>>> p5.toppings.add(pepperoni)
>>> p5.toppings.add(sausage)
>>> p5.toppings.add(olives)
>>> ...

How can I create a query that will return only return pizzas that have a topping of pepperoni (p1), or sausage (p2), or both pepperoni or sausage (p3)? I do not want pizzas that include pepperoni, sausage, and something else (p5).

Something like this will include a pizza that has pepperoni and olives (p4), which I don't want:

>>> Pizza.objects.filter(toppings__in=[pepperoni, sausage])

I can create a list of all toppings except the two I want and use that as an exclusion:

>>> toppings_i_do_not_want = Topping.objects.exclude(name__in=['Pepperoni', ['Sausage'])
>>> toppings_i_want = Topping.objects.filter(name__in=['Pepperoni', ['Sausage'])
>>> Pizza.objects.filter(toppings__in=toppings_i_want).exclude(toppings_i_do_not_want)

That will result in what I want, but it seems like the performance of such a query would suffer greatly if I'm only interested in two toppings but I have to pass ~100,000 other toppings into the exclude filter.

Is there a better way?

Answer 1

We can count the number of toppings that are pepperoni or sausage and compare that with the total number of related toppings, if the two match, and the number is larger than 0, then we can return such pizza:

from django.db.models import Count, Q

Pizza.objects.annotate(
    ntopping=Count('toppings')
).filter(
    ntopping__gte=1,
    ntopping=Count('toppings', filter=Q(toppings__in=[pepperoni, sausage]))
)

will exactly do what you want. It will return Pizza records for which "there exists a related topping that is in the list [pepperoni, sausage]". So for pizza's that have a pepperoni topping, a sausage topping, or both toppings.