Retrieving values from 2 different tables with Django's QuerySet

Question

For the following models:

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

class Pizza(models.Model):
    name = models.CharField(max_length=100)
    toppings = models.ManyToManyField(Toppping)

My data looks like the following:

Pizza and Topping tables joined:

ID  NAME        TOPPINGS
------------------------------------
1   deluxe      topping_1, topping_2
2   deluxe      topping_3, topping_4
3   hawaiian    topping_1

I want to get the pizza id along with their corresponding toppings for all pizza named deluxe. My expected result is:

1   topping_1
1   topping_2
2   topping_3
2   topping_4

The junction table is:

pizza_toppings
--------------
id    
pizza_id    
topping_id

Here's the SQL equivalent of what I want to achieve:

SELECT p.id, t.name
FROM pizza_toppings AS pt
INNER JOIN pizza AS p ON p.id = pt.pizza_id
INNER JOIN topping AS t ON t.id = pt.topping_id
WHERE p.name = 'deluxe'

Any ideas on what the corresponding Django Queryset looks like? I also want to sort the resulting toppings by name if the above is not challenging enough.

Answer 1

There is no direct way to get a pizza when you have a topping from the model above. You could do

pizzas = topping.pizza_set.all()

for all pizzas with a topping or probably (in case the topping exists on only one pizza with the name "deluxe")

pizza = topping.pizza_set.get(name="deluxe")

once you have a topping. Or, you could store the Pizza and the Topping in a list of tuples or a dictionary (if there are no duplicate toppings):

toppings = {}
pizzas = Pizza.objects.filter(name="deluxe")
for pizza in pizzas:
    for topping in pizza.toppings.all():
        toppings[topping.name] = pizza.name
sorted_toppings = toppings.keys()
sorted_toppings.sort()

Then you can fetch the pizza for a topping with the dictionary.

Answer 2

I don't think there is a clean solution to this, since you want data from two different models. Depending on your data structure you might want to use select_related to avoid hitting the database for all the toppings. Going for your desired result, I would do:

result = []
pizzas = Pizza.objects.select_related().filter(name='deluxe')
for pizza in pizzas:
    for toppings in pizza.toppings.all():
        result.append((pizza.pk, topping.name))

This would generate:

[
    (1, topping_1),
    (1, topping_2),
    (2, topping_3),
    (2, topping_4),
]

Now there are different ways to setup the data, using lists, tuples and dictionaries, but I think you get the idea of how you could do it.