CODEWITHSUNDEEP
djangoorm
Collin Anderson
Collin Anderson

Reputation: 15434

Filtering only on Annotations in Django

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?

Upvotes: 31

Views: 28886

Answers (6)

ivan_onys
ivan_onys

Reputation: 2372

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.

Upvotes: 10

pupil
pupil

Reputation: 328

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

Upvotes: 0

thinker3
thinker3

Reputation: 13291

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,
)

Upvotes: 11

Maccesch
Maccesch

Reputation: 2128

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'
})

Upvotes: 11

Paulo Scardine
Paulo Scardine

Reputation: 77251

You can try something like:

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

Upvotes: 0

czarchaic
czarchaic

Reputation: 6318

You can use the annotation variable in the filter.

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

Upvotes: 36

Related Questions