#25546: Duplicate queries on nested Prefetch objects with custom queries
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) | Keywords: prefetch,
Severity: Normal | prefetch_related
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Assuming these models
{{{
from django.db import models
class FastfoodChain(models.Model):
name = models.CharField(primary_key=True, max_length=100)
class Restaurant(models.Model):
name = models.CharField(primary_key=True, max_length=100)
chain = models.ForeignKey(FastfoodChain)
class RestaurantProfile(models.Model):
user = models.ForeignKey('auth.User')
restaurant = models.ForeignKey(Restaurant)
is_favorite = models.BooleanField(default=False)
}}}
the following test fails
{{{
from django.contrib.auth.models import User
from django.db.models.query import Prefetch
from django.test import TestCase
from fastfood import models
class PrefetchTestCase(TestCase):
def test_prefetch(self):
user = User.objects.create(username='test')
chain1, chain2, chain3, chain4 = [
models.FastfoodChain.objects.create(name='chain1'),
models.FastfoodChain.objects.create(name='chain2'),
models.FastfoodChain.objects.create(name='chain3'),
models.FastfoodChain.objects.create(name='chain4'),
]
restaurant11, restaurant12, restaurant21, restaurant31,
restaurant32, restaurant41 = [
models.Restaurant.objects.create(chain=chain1,
name='restaurant11'),
models.Restaurant.objects.create(chain=chain1,
name='restaurant12'),
models.Restaurant.objects.create(chain=chain2,
name='restaurant21'),
models.Restaurant.objects.create(chain=chain3,
name='restaurant31'),
models.Restaurant.objects.create(chain=chain3,
name='restaurant32'),
models.Restaurant.objects.create(chain=chain4,
name='restaurant41'),
]
p1, p2, p3, p4 = [
models.RestaurantProfile.objects.create(restaurant=restaurant11,
user=user, is_favorite=True),
models.RestaurantProfile.objects.create(restaurant=restaurant12,
user=user, is_favorite=False),
models.RestaurantProfile.objects.create(restaurant=restaurant21,
user=user, is_favorite=True),
models.RestaurantProfile.objects.create(restaurant=restaurant31,
user=user, is_favorite=True),
]
with self.assertNumQueries(3):
chains = models.FastfoodChain.objects.filter(
name__in=['chain1', 'chain2', 'chain4']
).prefetch_related(
Prefetch(
'restaurant_set',
models.Restaurant.objects.prefetch_related(
Prefetch(
'restaurantprofile_set',
models.RestaurantProfile.objects.filter(
user=user,
is_favorite=True,
),
to_attr='profiles',
)
),
to_attr='restaurants',
),
)
result_chain_1, result_chain_2, result_chain_4 = list(chains)
self.assertListEqual(result_chain_1.restaurants,
[restaurant11, restaurant12])
self.assertListEqual(result_chain_2.restaurants,
[restaurant21])
self.assertListEqual(result_chain_4.restaurants,
[restaurant41])
self.assertListEqual(result_chain_1.restaurants[0].profiles,
[p1])
self.assertListEqual(result_chain_1.restaurants[1].profiles,
[])
self.assertListEqual(result_chain_2.restaurants[0].profiles,
[p3])
self.assertListEqual(result_chain_4.restaurants[0].profiles,
[])
}}}
With this output
{{{
======================================================================
FAIL: test_prefetch (fastfood.tests.PrefetchTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/benjaminwohlwend/projects/prefetch_test/fastfood/tests.py",
line 57, in test_prefetch
self.assertListEqual(result_chain_4.restaurants[0].profiles, [])
File "/Users/benjaminwohlwend/.virtualenvs/prefetch-test/lib/python2.7
/site-packages/django/test/testcases.py", line 93, in __exit__
query['sql'] for query in self.captured_queries
AssertionError: 4 queries executed, 3 expected
Captured queries were:
SELECT "fastfood_fastfoodchain"."name" FROM "fastfood_fastfoodchain" WHERE
"fastfood_fastfoodchain"."name" IN ('chain1', 'chain2', 'chain4')
SELECT "fastfood_restaurant"."name", "fastfood_restaurant"."chain_id" FROM
"fastfood_restaurant" WHERE "fastfood_restaurant"."chain_id" IN ('chain1',
'chain2', 'chain4')
SELECT "fastfood_restaurantprofile"."id",
"fastfood_restaurantprofile"."user_id",
"fastfood_restaurantprofile"."restaurant_id",
"fastfood_restaurantprofile"."is_favorite" FROM
"fastfood_restaurantprofile" WHERE
("fastfood_restaurantprofile"."is_favorite" = 1 AND
"fastfood_restaurantprofile"."user_id" = 1 AND
"fastfood_restaurantprofile"."restaurant_id" IN ('restaurant11',
'restaurant12', 'restaurant21', 'restaurant41'))
SELECT "fastfood_restaurantprofile"."id",
"fastfood_restaurantprofile"."user_id",
"fastfood_restaurantprofile"."restaurant_id",
"fastfood_restaurantprofile"."is_favorite" FROM
"fastfood_restaurantprofile" WHERE
("fastfood_restaurantprofile"."is_favorite" = 1 AND
"fastfood_restaurantprofile"."user_id" = 1 AND
"fastfood_restaurantprofile"."restaurant_id" IN ('restaurant11',
'restaurant12', 'restaurant21', 'restaurant41'))
----------------------------------------------------------------------
Ran 1 test in 0.011s
}}}
The inner "prefetch" query is issued twice.
Note: I only used `primary_key=True` for nicer test output. The problem
happens either way. I tried this with Django 1.8.5 and 1.9a1. A complete
test project with the above code is attached.
--
Ticket URL: <https://code.djangoproject.com/ticket/25546>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.b2ed6d1b40f823e31887a7355278c29a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
