Re: Need help on a (maybe complex) query with aggregate/annotate

On Tue, Feb 21, 2012 at 01:10:28PM -0800, Enrico wrote:
>But your query counts all the books, even the bad ones. I only need to
>count the good ones...
>
>For example, if my books are:
>
>1. name: LOTR, rating: 10, publisher: A ...
>2. name: ASOIAF, rating: 10, publisher: A ...
>3. name: Twilight, rating 1, publisher: B ...
>
>and my publishers are:
>A and B
>
>Your query returns:
>[A, num_book=2]
>[B, num_book=1]
>
>the query on my first message returns:
>[A, num_book=2]
>
>and what I need is:
>[A, num_book=2]
>[B, num_book=0]
>
>where num_books means number of good books.

Sorry, I missed that detail in your question. You need to use the
.extra() queryset method to do what you are attempting. The
following will add a 'num_good_books' attribute to each Publisher
object:

qs = Publisher.objects.extra(select={'num_good_books': 'select count(*) from publisher_book where publisher_book.publisher_id = publisher_publisher.id and publisher_book.rating > 3.0'})
for o in qs:
print o.name, o.num_good_books

Note that the "publisher_" prefix in the SQL is the name of the
Django application in which your models reside. So if your Django
application is named "myapp", then you'd need to change the
"publisher_" prefix to "myapp_".

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