Django Count lines in a Subquery

user_1232682 0
Viewed 1789

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.

mysql django-orm django
1 Answers

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.

user_12326820
Related