Force INNER JOIN for Django Query

django, orm, python, sql

Solution

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.

Problem

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.

Original source