[FIXED] How to perform a __all_in query with Django?

Issue

I have three models

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

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

class Order(models.Model):
    must_have_toppings = models.ManyToManyField(Topping)

I want to find all Orders that match a certain Pizza. For this, I would like to do something like

orders = Order.objects.filter(must_have_toppings__all_in=my_pizza.toppings)

What I tried:

orders = Order.objects.filter(must_have_toppings__in=my_pizza.toppings) doesn't work, because Orders with just one of the Pizza's toppings in their must_have will be returned.

And:

orders = Orders.objects
for topping in my_pizza.toppings.all():
    orders = orders.filter(must_have_toppings=topping)

doesn't work, because it will return orders that have all the toppings from the pizza, even if some of the must_have_toppings are missing. For example, a Pizza with Tomatoes and Mushrooms will return an Order that needs Tomato, Pepper, and Mushroom.

How can I look for Orders where the must_have_toppings are ALL in the Pizza object?

(I am using MySql)

Solution

If you want to find orders where all the Toppings in an order belong to my_pizza, we can filter with:

from django.db.models import F, Q

toppings = my_pizza.toppings.all()

Order.objects.annotate(
    ntoppings=Count(
        'must_have_toppings',
        filter=Q(must_have_toppings__in=toppings)
    ),
    total=Count('must_have_toppings')
).filter(
    ntoppings=F('total')
)

We here first fetch all the toppings of my_pizza and then check if the number of toppings i the same as the number of toppings that belong to my_pizza, if that holds, we know that all toppings of the order are a member of that pizza.

We can also do the opposite and check if all toppings of the my_pizza belong to the Order:

from django.db.models import Count, Q

toppings = my_pizza.toppings.all()
ntoppings = len(toppings)

Order.objects.annotate(
    ntoppings=Count(
        'must_have_toppings',
        filter=Q(must_have_toppings__in=toppings)
    )
).filter(
    ntoppings=ntoppings
)

If an Order has more toppings than the pizza, than that order will still show up. So if the pizza has as toppings cheese and pineapple, than an order with cheese, salami and pineapple will match.

For an exact match where the order and my_pizza have exactly the same toppings, we can work with:

from django.db.models import Count, Q

toppings = my_pizza.toppings.all()
ntoppings = len(toppings)

Order.objects.annotate(
    ntoppings=Count(
        'must_have_toppings',
        filter=Q(must_have_toppings__in=toppings)
    ),
    total=Count('must_have_toppings')
).filter(
    ntoppings=ntoppings,
    total=ntoppings
)

All code snippets assume that a topping can only occur once.

Answered By - Willem Van Onsem