On 27 joulu, 11:32, Scott Maher <sc...@thereceptor.net> wrote: > Is it possible to filter by the ManyToMany fields? > > Suppose I have a Pizza model and a Topping model. Topping has a > ManyToManyField pointing to Pizza. I now have an instance of a Pizza > called mypizza. > > I would like to now search for all Pizzas with the EXACT same Toppings > as mypizza. Intuitively it would like something like this: > > Pizza.objects.filter(toppings=mypizza.toppings_set) > > This, naturally, does not work. Any ideas? I'm happy to do some fancy > black magic with the models to make it work, or do a custom SQL clause. > I just don't know enough SQL to do that. > > I'm also interested in reasonably alternative ways to model my data if I > have a potentially very large set of Pizzas however a limited (although > fairly lengthy, 20+) list of toppings that might change periodically. > > Thanks!
This is not the easiest query to perform using SQL. Something like the following query might work, I have tested it only quickly. select t2.pizza_id from (select pizza_id from pizza_toppings group by pizza_id having count(*) = (select count(*) from pizza_toppings where pizza_id = 2)) t1 inner join pizza_toppings as t2 on t1.pizza_id = t2.pizza_id inner join (select * from pizza_toppings where pizza_id=2) t3 on t2.topping_id = t3.topping_id group by t2.pizza_id having count(*) = (select count(*) from pizza_toppings where pizza_id = 2); The idea of the query is to first find out all the pizzas that have the same amount of toppings as the mypizza instance (assumed to have pizza_id=2 in the query). Then from those pizzas, list the toppings that are in mypizza. Finally require there are exactly as many toppings in the list as there are toppings in mypizza. I do not think there is any way to perform this query using Django ORM. I haven't used the group by capabilities of the ORM much, so maybe it is possible... -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
