How can I do this complex Django queryset correctly?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You can go the other way around:

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

CodePudding user response：

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.