Re: Order model objects by foreign key "set" count

[Scott Maher]

Margie Roginski wrote:

Say I have a Reader model that has a foreign key to a Book

class Reader(models.Model):
  book = models.ForeignKey(Book)


Now say I want to find all books and order them by the number of
readers.  Is that possible, ie something like this?

Book.objects.all().order_by(reader_set__count)

This syntax doesn't work, however.  Is this possible?

Margie
  
I can't give you specific code but I think that you want is under the 
Aggregation section of the documentation. Specifically I think you want 
to apply the Count object on the Book reader set. You were almost there. :)


http://docs.djangoproject.com/en/dev/topics/db/aggregation/#topics-db-aggregation
-- 
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.

Re: Comparing ManyToMany fields

[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

--

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.

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!

--

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.