Opened 4 years ago

Closed 3 years ago

#16439 closed Bug (fixed)

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

Reported by: dloewenherz 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 4 years ago by AndrewIngram

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 4 years ago by dloewenherz

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 4 years ago by aaugustin

  • Triage Stage changed from Unreviewed to Design 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 4 years ago by mtredinnick

  • Component changed from Database layer (models, ORM) to Documentation
  • Needs documentation set
  • Summary changed from select_related does not work with nullable fields to select_related does not work with nullable fields unless field is specified.
  • Triage Stage changed from Design decision needed to Accepted

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 3 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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