Search code examples
mysqldjangodjango-orm

Django Count lines in a Subquery


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.


Solution

  • 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.