CODEWITHSUNDEEP
django
Mikhail
Mikhail

Reputation: 9007

Django does a useless join when filtering

I have an optional foreign key for a parameter:

class Club(models.Model):
    ...
    locationid = models.ForeignKey(location_models.Location, null=True)
    ...

I want to find entries of club where this foreignkey is not set. Here's the ORM query:

print Club.objects.filter(locationid=None).only('name').query

Produces

SELECT `club_club`.`id`, `club_club`.`name` FROM `club_club`
LEFT OUTER JOIN `location_location`
ON (`club_club`.`locationid_id` = `location_location`.`id`)
WHERE `location_location`.`id` IS NULL

Same query is produced when I do filter(locationid_id__isnull=True)
What I want is to query on locationid_id without involving a JOIN. I know I can write raw SQL, but is there an ORM-al way of doing this?

Upvotes: 0

Views: 90

Answers (1)

knbk
knbk

Reputation: 53679

This seems to be quite a persistent issue, and the patch that fixed it had other side effects, so it never got applied to a release version of Django.

A solution to this is to use the extra method. This will require raw SQL, but only a limited amount and using SQL standards, so it should be compatible with all SQL databases:

location_null = '`%s`.`%s` IS NULL' % (Club._meta.db_table, Club.locationid.field.column)
Club.objects.extra(where=[location_null])

You can add this as a manager/queryset method for a more DRY solution.

The other option is to just take the performance hit. This is what I would recommend, unless benchmarking shows that the performance hit really is unacceptable in your specific case.

Upvotes: 1

Related Questions