CODEWITHSUNDEEP
pythondjango
killua8p
killua8p

Reputation: 314

Django: How to use select_related to INNER JOIN FK's FK

29 Dec: updated models

I have got three models as follows:

class Job(models.Model):
    job_number = models.CharField(max_length=20, primary_key=True)

class Project(models.Model):
    job = models.ForeignKey(Job, null=True) # updated (null=True)***
    source = models.ForeignKey(Source) # added***

class Task(models.Model):
    project = models.ForeignKey(Project)

class Source(models.Model): # added***
    blahblah...

And I would like to get the job number for a task. Something like below:

job = Job.objects.all().select_related()
jobno = job[0].project.job.job_number

I'm not sure how many times the query above will hit the DB. But I guess it will be more than twice, won't it?

select_related can only pre-cache the foreign key for 2 tables to my understanding. Any one can suggest the best practice in this case to reduce the number of times hitting the DB?

Upvotes: 1

Views: 4382

Answers (2)

Iasmini Gomes
Iasmini Gomes

Reputation: 795

You can use a filter:

task = Task.objects.all().select_related().filter(
    project__id__isnull=False, 
    job__id__isnull=False)

Upvotes: 1

catavaran
catavaran

Reputation: 45575

select_related() joins all these three models in one query:

>>> from app.models import Task
>>> task = Task.objects.all().select_related()[0]
>>> task.project.job.job_number
u'123'
>>> from django.db import connection
>>> len(connection.queries)
1
>>> connection.queries
[{u'time': u'0.002', u'sql': u'QUERY = u\'SELECT "app_task"."id", "app_task"."project_id", "app_project"."id", "app_project"."job_id", "app_job"."job_number" FROM "app_task" INNER JOIN "app_project" ON ( "app_task"."project_id" = "app_project"."id" ) INNER JOIN "app_job" ON ( "app_project"."job_id" = "app_job"."job_number" ) LIMIT 1\' - PARAMS = ()'}]
>>>

Readable SQL:

SELECT "app_task"."id", "app_task"."project_id", "app_project"."id",
       "app_project"."job_id", "app_job"."job_number"
FROM "app_task"
INNER JOIN "app_project" ON ( "app_task"."project_id" = "app_project"."id" )
INNER JOIN "app_job" ON ( "app_project"."job_id" = "app_job"."job_number" )

Upvotes: 1

Related Questions