Complex M2M filtering using Django's ORM

Question

I have the following models:

class Sauce(models.Model):
    ...

class Topping(models.Model):
    ...

class Pizza(models.Model):
    sauces = models.ManyToManyField(Sauce, related_name='pizzas')
    toppings = models.ManyToManyField(Topping, related_name='pizzas')

Now, lets say I want to query all the pizzas given a list of toppings and sauces. For example:

sauces_ids = [1, 2]
toppings_ids = [1, 2]

What I am doing right now in my API view is as follows:

pizzas = Pizza.objects.filter(restaurant=restaurant)

if request.data.get('sauces_ids', []):
    pizzas = pizzas.filter(
        sauces__in=
        request.data['sauces_ids']
    )

if request.data.get('toppings_ids', []):
    pizzas = pizzas.filter(
        toppings__in=
        request.data['toppings_ids']
    )

return pizzas.distinct()

There was a duplication problem which I solved using the distinct() function. However, now I am facing a different issue. I have 2 pizzas in my database:

• Pizza 1 with sauces = [1, 2], toppings = [1, 2]
• Pizza 2 with sauces = [1, 2], toppings = [1]

With my above query parameters, I would like to return only Pizza 1 as the 2 M2M lists match exactly. However, the query I wrote is returning both the pizzas. How do I solve this? Thanks for any help.

Moreover, is this an efficient way to do this?

Answer 1

This is because the query that you are doing will return Pizzas that have the topping 1 or topping 2 AND sauce 1 or sauce 2.

Because Pizza 1 has topping 1 and 2, AND sauce 1 and 2 you get it.
Because Pizza 2 has topping 1, AND sauce 1 and 2 you get it.

Basically, if I am not wrong you can do the following:

pizzas = Pizza.objects.filter(restaurant=restaurant)

if request.data.get('sauces_ids', []):
    sauces = request.data.get('sauces_ids'):
    for sauce in sauces:
        pizzas = pizzas.filter(
            sauces__pk=sauce
        )

if request.data.get('toppings_ids', []):
    toppings = request.data.get('toppings_ids'):
    for topping in toppings:
        pizzas = pizzas.filter(
            toppings__pk=topping
        )

return pizzas.distinct()

I wouldn't worry too much about efficiency, because since the QuerySets are lazy, you will end up performing a small amount of queries even if your sauces/topping list is large