CODEWITHSUNDEEP
sqldjangopostgresql
deadlock
deadlock

Reputation: 7330

Django: Number of ForeignKey Database Queries

I would just like to clarify something so that I can optimize my Django ORM queries to Postgres through the psycopg2 adapter.

Let's for instance say I have three models:

from django.db import models   

class City(models.Model):
     #
     pass

class Person(models.Model):
     hometown = models.ForeignKey(City)

class Book(models.Model):
     author = models.ForeignKey(Person)

Now let's do the following query:

book = Book.objects.get(pk = 4)

Now if I wanted to know the raw ID of the author, all I would need to do is this

>>> raw_id = book.author_id
>>> print raw_id
3

My question is, does book.author_id make another hit to the database or does it just grab it from memory since it was already grabbed before hand from the get query? From my understanding, there should only be one trip for this query correct?

Upvotes: 2

Views: 76

Answers (1)

dr jimbob
dr jimbob

Reputation: 17751

You are correct, book.author_id does not make another hit on the database, the foreign key ID is stored in book. You can see check this yourself if you do go to the shell with DEBUG=True setting:

>>> from django.db import connection

>>> connection.queries
[]

>>> book = Book.objects.get(pk=4)

>>> connection.queries
[{u'sql': u'SELECT "yourapp_book"."id", "yourapp_book"."author_id" FROM "yourapp_book" WHERE "yourapp_book"."id" = 4 ',
u'time': u'0.003'}]

>>> book.author_id
7

>>> connection.queries
[{u'sql': u'SELECT "yourapp_book"."id", "yourapp_book"."author_id" FROM "yourapp_book" WHERE "yourapp_book"."id" = 4 ',
u'time': u'0.003'}]

>>> book.author
<Author: Mark Twain>

>>> connection.queries
[{u'sql': u'SELECT "yourapp_book"."id", "yourapp_book"."author_id" FROM "yourapp_book" WHERE "yourapp_book"."id" = 4 ',
 u'time': u'0.003'},
 {u'sql': u'SELECT "yourapp_author"."id", "yourapp_author"."name" FROM "yourapp_author" WHERE "yourapp_author"."id" = 7 ',
 u'time': u'0.001'}]

connection.queries lists all the SQL queries being sent through the ORM in the django shell while DEBUG is on. Note only one query was made even after doing book.author_id, but a second query was made when we did book.author as django had to go back to the database to fetch the author's name to display.

You can avoid that second database query by selecting the book via book = Book.objects.select_related('author').get(pk=4).

>>> connection.queries
[]

>>> book = Book.objects.select_related('author').get(pk=4)

>>> connection.queries
[{u'sql': u'SELECT "yourapp_book"."id", "yourapp_book"."author_id", 
  "yourapp_author"."id", "yourapp_author"."name"
  FROM "yourapp_book" LEFT OUTER JOIN "yourapp_author" ON 
  ( "yourapp_book"."author_id" = "yourapp_author"."id" ) WHERE "yourapp_book"."id" = 4 ',
  u'time': u'0.003'}]

>>> book.author
<Author: Mark Twain>

>>> connection.queries
[{u'sql': u'SELECT "yourapp_book"."id", "yourapp_book"."author_id", 
  "yourapp_author"."id", "yourapp_author"."name"
  FROM "yourapp_book" LEFT OUTER JOIN "yourapp_author" ON 
  ( "yourapp_book"."author_id" = "yourapp_author"."id" ) WHERE "yourapp_book"."id" = 4 ',
  u'time': u'0.003'}]

I should also note that for debugging the number of queries actually made in a running django application during testing that the tool django-debug-toolbar is invaluable.

Upvotes: 4

Related Questions