[Django]-Django Count lines in a Subquery

by

15👍

✅

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.

👤Serafim Suhenky

Leave a comment