CODEWITHSUNDEEP
pythonsqldjangoorm
jarcoal
jarcoal

Reputation: 1455

Force INNER JOIN for Django Query

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.

Upvotes: 6

Views: 5636

Answers (2)

Anton Backer
Anton Backer

Reputation: 364

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.

Upvotes: 20

spookylukey
spookylukey

Reputation: 6576

You generally can't get this level of control over the queries and joins that Django executes.

Django doesn't do really clever things like notice that the filter condition means you can get away with an INNER JOIN at this point - it has to produce queries that are correct in the general case, which in this case means a LEFT OUTER JOIN as far as I can tell.

So you are probably going to need raw SQL.

Upvotes: 0

Related Questions