How can I do this complex Django queryset correctly?

Question

Let's say I have these three models:

class Author(models.Model):
   name = models.CharField(max_length=64)


class Book(models.Model):
   author = models.ForeignKey(
        Author,
        blank=True,
        null=True,
        on_delete=models.SET_NULL
    )
   name = models.CharField(max_length=64)


class Store(models.Model):
   books = models.ManyToManyField(Book)
   name = models.CharField(max_length=64)

I don't know the author of some books. In these cases, the author is null.

When I query Store, I would like to know how many books each store has and sort them. So my queryset is something like this:

Store.objects.all().annotate(books_count=Count('books')).order_by('-books_count')

Now, what if I want to count only the books that have an author?

I tried this queryset, but it is clearly not correct:

filter = Q(books__author__isnull=False)
Store.objects.annotate(books_count=Count(filter)).all().order_by('-books_count')

Does anyone know the correct way to do this query?

Does this answer your question? [Django 1.11 Annotating a Subquery Aggregate](https://stackoverflow.com/questions/42543978/django-1-11-annotating-a-subquery-aggregate) — Ivan Starostin, Nov 19 '22 at 09:50
Filter books with `OuterRef` and author predicate, then annotate stores with count based on this `Subquery`. — Ivan Starostin, Nov 19 '22 at 09:52

score 1 · Answer 1 · answered Nov 19 '22 at 09:49

1

You can go the other way around:

Book.objects.filter(author__isnull=False).values('store_set').annotate(count=Count('store_set')).order_by('-count')

answered Nov 19 '22 at 09:49

lucutzu33

3,470
1
10
24

Yery cs · Accepted Answer · 2022-11-19T17:12:37.167

1

I believe following query is what you are looking for.

books_count = (
    Book.objects.filter(
        store=OuterRef("pk"),
        author__isnull=False,
    )
    .annotate(count=Func(F("id"), function="COUNT"))
    .values("count")
)
Store.objects.annotate(count=Subquery(books_count)).order_by("-count")

edited Nov 19 '22 at 17:12

answered Nov 19 '22 at 14:41

Yery cs

302
1
9

Swift · Answer 3 · 2022-11-19T13:51:53.730

0

I'll take a bit of a wild guess, but im thinking your usage of a Q object might be incorrect inside of theCount.

Count is expecting an expression like object, this could be a string, F, OuterRef, SubQuery and so on.

Store.objects.filter(filter).annotate(
     books_count=Count("books")
).order_by('-books_count')

I think this is what you're after.

edited Nov 19 '22 at 13:51

answered Nov 19 '22 at 13:42

Swift

1,663
1
10
21

Thanks for the answer. Yes, I understand that using Q is not correct. I need something a bit different. Your query will filter the stores, but I want all of them. What I need is to filter the books, which are counted. I am reading the documentation on query expressions, but I still didn't find the right way to implement this query. – Adriano174 Nov 19 '22 at 13:59

How can I do this complex Django queryset correctly?

3 Answers3