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.
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))
.order_by()
will cancel ordering if any.values('parent')
will introduce right grouping.annotate(count=Count('pk'))
Will annotate (ad to each line) the answer we're looking for.values('count')
will limit rows to the count
exclusivelyCoalesce
will return first not null value or zeroThat is tricky to do from Django but pretty efficient.