akaariai wrote:
> 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...
>
Akaari,
The logic of your method is slowly what I came up with eventually but I
didn't have your level of awesome SQL wizardry to implement it. Thanks
much! Someone was able to figure out how to implement it in the ORM.
Here it is:
|qs =
Pizza.objects.annotate(toping_count=Count("toppings")).filter(toping_count=my_pizza.toppings.count())
for toping in my_pizza.toppings.all():
qs = qs.filter(toppings=toping)
|
The key was to annotate the table first so we can match pizzas with
topping sets of equal cardinality. I am told that doing a full table
annotate like this might be kind of slow though.
Thanks,
--sm
--
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.
