Given the following code:
class Customer(models.Model):
name = models.CharField(max_length=30)
class Project(models.Model):
name = models.CharField(max_length=30)
for p in Project.objects.all():
print (p.name, p.customer.name)
I then create the following objects:
customer 1
project 1
project 2
project 3
customer 2
project 1
project 2
project 3
The for loop prints the result is as expected:
Project 1 Customer 1
Project 2 Customer 1
Project 3 Customer 1
Project 4 Customer 2
Project 5 Customer 2
Project 6 Customer 2
By checking the connection.queries, I see that django has executed 7 queries:
'SELECT "myapp_project"."id", "myapp_project"."customer_id", "myapp_project"."name", "myapp_project"."is_active" FROM "myapp_project"'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 1 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 1 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 1 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 2 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 2 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 2 LIMIT 21
But my question is, why does the django ORM repeat the same query 3 times to retrieve the customer 1 associated with the projects 1, 2 and 3? I thought that the Customer data would be stored in the cache, and only 3 queries would be executed in total, like this:
'SELECT "myapp_project"."id", "myapp_project"."customer_id", "myapp_project"."name", "myapp_project"."is_active" FROM "myapp_project"'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 1 LIMIT 21'
'SELECT "myapp_customer"."id", "myapp_customer"."name", "myapp_customer"."is_active" FROM "myapp_customer" WHERE "myapp_customer"."id" = 2 LIMIT 21'
I would bet that this is by design. What is the rationale behind this behavior?
Thanks
(PS: I know that could have used select_related and use only one query joining the two entities, but this is more of a conceptual question):
Project.objects.select_related('customer')