Django Count lines in a Subquery

Question

I need to count lines in a subquery and here is my solution for sqlite.

class SQCount(Subquery):
    """Count lines in subquery"""
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = models.IntegerField() 

sub = MyModel.objects.filter(user=OuterRef(OuterRef('id'))).values('id')
qs = qs.annotate(count_total=SQCount(sub))

It works great for sqlite but not for MySQL (complains about Unknown column in 'where' clause). Any help appreciated.

Why is `user` not a `ForeignKey` to `MyModel`? That would make it more elegant in Django and (likely) more efficient at the database side. — Willem Van Onsem, Jan 06 '21 at 17:18
`user` is a ForeignKey to `MyModel`. But I'd like to use subquery here because in reality my requests have to use subqueries (in question you can see simplified code) — Serafim Suhenky, Jan 06 '21 at 17:32

score 15 · Accepted Answer · edited May 23 '23 at 11:51

Here is the right way to count lines in subquery in Django

subquery = Subquery(
    Child.objects.filter(
        parent_id=OuterRef('id')
    )
    .order_by()
    ).values(
        'parent'
    ).annotate(
        count=Count('pk')
    ).values(
        'count'
    ), 
    output_field=IntegerField(),
)
Parent.objects.annotate(child_count=Coalesce(subquery, 0))

The .order_by() will cancel ordering if any
First values .values('parent') will introduce right grouping
.annotate(count=Count('pk')) Will annotate (ad to each line) the answer we're looking for
Second values .values('count') will limit rows to the count exclusively
Coalesce will return first not null value or zero

That is tricky to do from Django but pretty efficient.

The django-sql-utils library removes a lot of the boiler plate so that it's not so tricky Parent.objects.annotate(child_count=SubqueryCount('child')) — Brad Martsberger, Jan 13 '21 at 17:32

Django Count lines in a Subquery

1 Answers1

Linked