Django Query, find most common value for field on a many to many relation

user_2898713 0
Viewed 441

Given these models (where a Restaurant has many Pizzas and Pizzas have many Toppings)

class Restraunt(Model)

class Topping(Model):
  name = Charfield()

class Pizza(Model):
  in_resteraunt = ForeignKey(Resteraunt) 
  toppings = ManyToMany(topping, related_name='on_pizza')

I'm trying to figure out how: Given a Restaurant, across all its Pizzas, what is the most common topping?

One thing I came up with is something like this (where R = the restaurant)

Topping.objects.filter(on_pizza__restaurant=R).annotate(the_count=Count('on_pizza')).order_by('-the_count')[0]

The problem here is that I'm not sure if the on_pizza is unique to the Restaurant object, basically a topping object can be on a pizza at multiple restaurants I need it to be unique. Here I'm starting from the Topping, is there a way I could start from a queryset of pizzas?

I've looked into subquery expressions but don't really see how to implement

mysql django python database
1 Answers

The problem here is that I'm not sure if the on_pizza is unique to the Restaurant object, basically a topping object can be on a pizza at multiple restaurants I need it to be unique.

The query is correct. You filter on the restaurant, so that means that in the Count, it will only count Pizzas that are for the restaurant R. So this will indeed calculate the most common topping for pizzas made in restaurant R.

The query will thus look like:

SELECT topping.*, COUNT(pizza.pk)
FROM topping
LEFT OUTER JOIN pizza_toppings ON pizza_toppings.topping_id = topping.id
LEFT OUTER JOIN pizza ON pizza.id = pizza_toppings.pizza_id
WHERE pizza.restaurant_id = id_of_r
GROUP BY topping.pk
ORDER BY COUNT(pizza.pk)
LIMIT 1
user_675790
Related