Comparing ManyToMany fields

[Scott Maher]

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!

[Michael Jenkinson]

Hi

a pizza has a one to many relationship with its topping. in a list of pizzas there could be some that have the same topping, like thick, thin etc.

the query would look like

select pizza from pizzas where topping = xyz

cheers

michael

Pizza.objects.filter(toppings=mypizza.toppings_set)

Thanks!

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[akaariai]

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...

[Scott Maher]

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