Filtering only on Annotations in Django

Question

Taking the example from: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#filter-and-exclude

Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

Is there anyway to have the filter only apply to the annotation, so it would return all publishers, with some having a num_books=0?

So, you want a list of all publishers, each annotated with their number of highly-rated books? — Christian Oudard, Jan 19 '10 at 20:08
Yes, and that list should include publishers that have no books or only books with low ratings. — Collin Anderson, Jan 20 '10 at 16:22

score 34 · Answer 1 · answered Jan 19 '10 at 20:31

34

You can use the annotation variable in the filter.

publishers=Publisher.objects.annotate(num_books=Count('book')).filter(num_books__gte=2)

answered Jan 19 '10 at 20:31

czarchaic

6,268
29
23

I mean, I want all publishers to be returned, and each publisher should be annotated with the number of books with high ratings. So, included in the publisher list should be publishers with less than 2 books with high ratings, and publishers with only low rated books, and publishers who have no books at all. Let me know if I can clarify that better. – Collin Anderson Jan 22 '10 at 14:09

score 11 · Answer 2 · answered May 31 '11 at 12:33

11

Hm, I think you have to use an extra clause:

Publisher.objects.extra(select={
    'num_books': 'SELECT COUNT(*) ' + \
                 'FROM <your_app>_book ' + \
                 'WHERE <your_app>_book.publisher_id = ' + \
                       '<your_app>_publisher.id AND ' + \
                       'rating > 3.0'
})

answered May 31 '11 at 12:33

Maccesch

1,998
1
18
27

This is the best answer I've seen so far. It would be nice if I didn't have to use raw sql though. – Collin Anderson May 31 '11 at 16:24
That's true! This shouldn't cause any compatibility problems, though. But please let me know, if you discover a way to do this without raw sql! – Maccesch May 31 '11 at 17:40
1

You can probably use QuerySet._as_sql(connection) method, to generate raw query by Django itself. Little hack but still better than write it itself. – farincz Jan 29 '14 at 13:06
this seems exactly as if I added a property to my model to do this filter, and would cause n+1 issues, right? – Nathan Tregillus Jul 14 '15 at 19:52
2

@NathanTregillus It would not create any additional queries, as it is all done in SQL. It would be a little slower just because the query is more complex, but nothing like one query for every object. – semicolon Jul 14 '15 at 23:00
1

This used to be a good answer, but is outdated in 2017. The [django docs for extra](https://docs.djangoproject.com/en/1.11/ref/models/querysets/#extra) say this method is deprecated, and to only use it as a last resort. They go as far as asking you to open a ticket for your use case so they can support it with `annotate`. – ckeeney Nov 17 '17 at 06:54

score 10 · Answer 3 · edited Jul 06 '16 at 05:02

10

from django.db import models

Publisher.objects.annotate(
    num_books=models.Sum(
        models.Case(
            models.When(
                book__rating__gt=3.0,
                then=1,
            ),
            default=0,
            output_field=models.IntegerField(),
        )
    )
).filter(
    num_books=0,
)

edited Jul 06 '16 at 05:02

solarissmoke

30,039
14
71
73

answered Jun 14 '16 at 09:37

thinker3

12,771
5
30
36

That looks close, except don't filter by "num_books=0" at the end. All publishers should be returned. – Collin Anderson Jun 14 '16 at 13:32

score 7 · Answer 4 · answered Feb 01 '19 at 14:57

7

Starting Django 2.0 one may use filter parameter of aggregation function:

from django.db.models import Q    
Publisher.objects.annotate(num_books=Count('book', filter=Q(book__rating__gt=3.0)))

The answer is based on a cheat sheet.

answered Feb 01 '19 at 14:57

ivan_onys

2,282
17
21

pupil · Answer 5 · 2017-06-10T16:30:45.783

0

I just facing this kind of problem. And if my interpreation for the problem and expected solution is correct, this is my working solution:
Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0) Just swap filter & annotate position. This is done in Django version 1.9

edited Jun 10 '17 at 16:30

answered Jun 09 '17 at 06:40

pupil

318
2
16

1

That's the exact same code I have in my question (not even swapped). The problem I was having is that it doesn't show publishers that have 0 books. I don't want to filter the publishers at all, I just want to filter the count. – Collin Anderson Jun 09 '17 at 13:21
Sorry, I forgot to edit your sample code. But this is what I was implementing when I want to use filter & annotate in my queryset. And unfortunately, I haven't read your question clearly about 0 book publisher. – pupil Jun 10 '17 at 16:38

score 0 · Answer 6 · answered May 31 '11 at 12:32

0

You can try something like:

Book.objects.values('publisher').annotate(num_books=Count('id'))

answered May 31 '11 at 12:32

Paulo Scardine

73,447
11
124
153

2

That would be a count of all books, not just the ones that have a rating greater than 3. – Collin Anderson May 31 '11 at 16:23

Filtering only on Annotations in Django

6 Answers6

Linked