Using Subquery to annotate a Count

Question

Please help me I've been stuck on this for way too long :(

What I want to do:

I have these two models:

class Specialization(models.Model):
    name = models.CharField("name", max_length=64)
class Doctor(models.Model):
    name = models.CharField("name", max_length=128)
    # ...
    specialization = models.ForeignKey(Specialization)

I would like to annotate all specializations in a queryset with the number of doctors that have this specialization.

My Solution so Far:

I went through a loop and I made a simple: Doctor.objects.filter(specialization=spec).count() however this proved to be too slow and inefficient. The more I've read the more I realized that it would make sense to use a SubQuery here to filter the doctors for the OuterRef specialization. This is what I came up with:

doctors = Doctor.objects.all().filter(specialization=OuterRef("id")) \
    .values("specialization_id") \
    .order_by()
add_doctors_count = doctors.annotate(cnt=Count("specialization_id")).values("cnt")[:1]

spec_qs_with_counts = Specialization.objects.all().annotate(
    num_applicable_doctors=Subquery(add_doctors_count, output_field=IntegerField())
)

The output I get is just 1 for every speciality. The code just annotates every doctor object with its specialization_id and then annotates the count within that group, meaning it will be 1.

This doesn't make complete sense to me unfortunately. In my initial attempt I used an aggregate for the count, and while it works on its own it doesn't work as a SubQuery, I get this error:

This queryset contains a reference to an outer query and may only be used in a subquery.

I posted this question before and someone suggested doing Specialization.objects.annotate(count=Count("doctor"))

However this doesn't work because I need to count a specific queryset of Doctors.

I've followed these links

However, I'm not getting the same result:

• https://docs.djangoproject.com/en/1.11/ref/models/expressions/

• https://medium.com/@hansonkd/the-dramatic-benefits-of-django-subqueries-and-annotations-4195e0dafb16

• Django 1.11 Annotating a Subquery Aggregate

If you have any questions that would make this clearer please tell me.

Answer 1

Problem

The problem is that Django adds GROUP BY as soon as it sees using an aggregate function.

Solution

So you can just create your own aggregate function but so that Django thinks it is not aggregate. Just like this:

doctors = Doctor.objects.filter(
    specialization=OuterRef("id")
).order_by().annotate(
    count=Func(F('id'), function='Count')
).values('count')

spec_qs_with_counts = Specialization.objects.annotate(
    num_applicable_doctors=Subquery(doctors)
)

The more details on this method you can see in this answer: https://stackoverflow.com/a/69020732/10567223

Useful information can also be found in the docs about using aggregates within a subquery expression and func expressions.