Using select_related to get values two models away

Question

Lets say I have the following:

class Model1(Model):

    field1 = ForeignKey(Model2)
    query_field = IntegerField()

class Model2(Model):
    field2 = ForeignKey(Model3)

class Model3(Model)
    field3 = SomeDesiredValue

Now I want to do a query on the Model1 table and pull out field3 from Model3. My question is on performance of select_related.

If I do

query = Model1.objects.filter(query_field=filter_paramter).select_related('field1')

I'm assuming the following will still hit the database?

query[0].field1.field2.field3

Can I do the following to make it faster?

query = Model1.objects.filter(query_field=filter_paramter).select_related('field1__field2')

FWIW, you can view the actual SQL query that gets executed by saying `print query.query` (using the variable in your example) -- [here](http://stackoverflow.com/q/971667/2428558) is a related question. — Jackall, Feb 06 '15 at 10:13

score 3 · Accepted Answer · answered Feb 06 '15 at 09:22

3

Yes, the first query leads to two database hits and the second query will be handled with one SQL-query with two INNER JOINS:

SELECT "app_model1"."id", "app_model1"."field1_id",
       "app_model1"."query_field", "app_model2"."id",
       "app_model2"."field2_id", "app_model3"."id",
       "app_model3"."field3"
FROM "app_model1"
INNER JOIN "app_model2" ON ( "app_model1"."field1_id" = "app_model2"."id" )
INNER JOIN "app_model3" ON ( "app_model2"."field2_id" = "app_model3"."id" )
WHERE "app_model1"."query_field" = 1 LIMIT 1

answered Feb 06 '15 at 09:22

catavaran

44,703
8
98
85

Thanks for the answer. Quick clarification, when I use `.select_related('field1__field2')` does it pull everything from `Model2`? Or just what it needs to do the join with the `Model3` table? – sedavidw Feb 06 '15 at 12:37
1

It loads everything for both `Model2` and `Model3` models. So `obj = query.first() \n obj.field1.some_attr \n obj.field1.field2.other_attr \n obj.field1.field2.field3` - that all will be handled with a single SQL-query. – catavaran Feb 06 '15 at 13:32

score 1 · Answer 2 · answered Feb 06 '15 at 10:02

class Model1(Model):

    field1 = ForeignKey(Model2, related_name="model2")
    query_field = IntegerField()

class Model2(Model):
    field2 = ForeignKey(Model3, related_name="model3")

class Model3(Model)
    field3 = SomeDesiredValue

and do something like this would be better.

mdls1 = Model1.objects.filter(query_field=filter_paramter).first()

related_model3s = mdls1.field1.model3.all()

this will get all field2 from Model2(this means all related Model3 with Model2) If you need any help just ask :)

Using select_related to get values two models away

2 Answers2