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

Thanks Micheal for the response!

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.

I'm trying to do this with a single query because i need to iterate
over the queryset in one template with something like:
{% for p in publishers %}
{{ p.name }} - {{ p.number_good_books }}
{% endfor %}
Right now I'm using a custom method in the Publisher model
( get_number_of_good_books() ).
It works but it's really slow (one query for each publisher to count
the number of good books).

Ciao

On Feb 21, 7:50 pm, Michael Elkins <michael.elk...@gmail.com> wrote:
> On Feb 21, 6:11 am, Enrico <poe...@gmail.com> wrote:
>
> > This query:
> > Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('bo ok'))
> > returns all the publishers with at least one good book (ranked > 3)
> > with annotated the number of good books for each publisher.
>
> > How can i modify the query to get the list of ALL publishers with
> > annotated the number of good books for each publisher?
> > In other words I want to keep in the results also the Publishers
> > without good books (num_books = 0).
>
> Hi Enrico,
>
> The Django documentation has an example of what you are trying to do:
>
> https://docs.djangoproject.com/en/1.3/topics/db/aggregation/#generati...
>
> The answer to your specific question is to do it this way:
>
> qs = Publisher.objects.annotate(num_books=Count('book'))
> for o in qs:
>   print o.num_books

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