Opened 5 years ago

Closed 4 years ago

#16439 closed Bug (fixed)

select_related does not work with nullable fields unless field is specified.

Reported by: Dan Loewenherz Owned by: nobody
Component: Documentation Version: 1.3
Severity: Normal Keywords: db
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: yes
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Say I have the following model:


class Book(models.Model):
      owner = models.ForeignKey(Owner, related_name='books')

Book.objects.select_related() will pull in all owner data. HOWEVER, when the owner field is nullable, no joins occur.

class Book(models.Model):
    owner = models.ForeignKey(Owner, null=True, blank=True, related_name='books')

Running a join in my database involving a nullable field works exactly as would one on a non-nullable field, so I can't really see a good reason for this to happen.

Change History (5)

comment:1 Changed 5 years ago by AndrewIngram

select_related will follow nullable foreignkeys if you specify the fieldname manually, eg:

Books.objects.select_related('owner')

I'm sure there's a good reason why this doesn't happen by default, but the behaviour is documented.

comment:2 Changed 5 years ago by Dan Loewenherz

Thanks for the tip!

In my actual model there are about 6 ForeignKey relationships (all of which are accessed for the request). That means I have to manually specify each one I want to perform a join on, which is a little tedious/unclean.

comment:3 Changed 5 years ago by Aymeric Augustin

Triage Stage: UnreviewedDesign decision needed

Digging into the docs and code, there is no explanation of this behavior.

The condition is in django.db.models.query_utils.select_related_descend.

comment:4 Changed 5 years ago by Malcolm Tredinnick

Component: Database layer (models, ORM)Documentation
Needs documentation: set
Summary: select_related does not work with nullable fieldsselect_related does not work with nullable fields unless field is specified.
Triage Stage: Design decision neededAccepted

A documentation patch is welcome for this (although I thought it was documented originally; may have been a friendly fire casualty). For performance reasons, we will never traverse nullable relations by default, as it requires using left outer joins, instead of merely inner joins, and the former are significantly slower. It is intentional that you can manually tell your code to traverse nullable relations by passing in the field names, so there's no behavioural bug here, just a documentation one.

comment:5 Changed 4 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

Quoting from the current documentation (https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.select_related):

Note that, by default, select_related() does not follow foreign keys that have null=True.

So no documentation patch required. The reason it's like this is because nullable joins require an outer join between tables and is sufficiently performance impacting to not do by default.

Note: See TracTickets for help on using tickets.
Back to Top