Force INNER JOIN for Django Query

Question

Here is my schema:

City --> Photographer

I'm trying to get a list of cities that have at least one photographer, and return the photographer count for the cities.

Here is the queryset I'm working with:

City.objects.annotate(photographer_count=aggregates.Count('photographers')).filter(photographer_count__gt=0).order_by('-photographer_count')

This works exactly as I would expect it too, except for some reason Django chooses to make the join between city/photographer with a left outer join. If I grab the SQL text and simply change the "left outer" to "inner", the query goes from ~11 seconds to 200ms with identical results.

I've tried putting a filter in front of the annotate to hint to Django that it should be inner joining, but that didn't work.

Any Django query voodoo I can perform on this to get that inner join? I realize I can use straight SQL, but would prefer to go through the ORM.

Answer 1

By default, a LEFT JOIN is generated so that Django can get you rows even for cities with zero photographers. If you know you don't want those, here's a trick to force Django to generate an INNER JOIN:

City.objects.filter(
    photographer__isnull=False
).annotate(
    photographer_count=aggregates.Count('photographers')
).filter(
    photographer_count__gt=0
).order_by(
    '-photographer_count'
)

Specifically, that first filter tells Django that an INNER JOIN is safe. It must come before the annotate() call.