Annotate Django Foreign Key model instance

Question

Is it possible in Django to annotate a Foreign Key instance?

Suppose I have the following models:

class BaseModel(models.Model):
  pass

class Foo(models.Model):
  base_model = models.ForeignKey('BaseModel', related_name='foos')

class Bar(models.Model):
  base_model = models.ForeignKey('BaseModel', related_name='bars')

I want to count the Bars belonging to a BaseModel attached to a Foo, that is:

foos = Foo.objects.all()
for foo in foos:
  foo.base_model.bars_count = foo.base_model.bars.count()

Is it possible in a single query? The following code is syntactically wrong:

foos = Foo.objects.annotate(
  base_model.bars_count=Count('base_model__bars')
)

This one would perform that job in a single query:

foos = Foo.objects.annotate(
  base_model_bars_count=Count('base_model__bars')
)
for foo in foos:
  foo.base_model.bars_count = foo.base_model_bars_count

Is there a way with a single query without the loop?

Answer 1

Is there a way with a single query without the loop?

Not at the time of writing. You could use a Prefetch object, but that would result in two queries then.

But likely the for loop is not much of a problem: the data is already there, so the loop itself will likely take a few microseconds.

What you however forgot (likely only in your example) is to .select_related('base_model'), so:

from django.db.models import Count

foos = Foo.objects.select_related('base_model').annotate(
    base_model_bars_count=Count('base_model__bars')
)
for foo in foos:
    foo.base_model.bars_count = foo.base_model_bars_count

I agree that it is not very convenient, but performance-wise, the loop will likely not matter much, given you do this after slicing/pagination.