id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35947	prefetch_related makes duplicate queries for the same records by different relations	Jake Douglas		"Prefetching the same records via different relations using `prefetch_related` results in duplicated queries for the same set of records. This is a problem because many web applications traverse relations by many different paths, even in the same request. The smallest example I could reproduce follows:

{{{
from django.db import models

# Create your models here.
class House(models.Model):
    pass

class Story(models.Model):
    house = models.ForeignKey(House, related_name = ""stories"", on_delete = models.CASCADE)

class Room(models.Model):
    story = models.ForeignKey(Story, related_name = ""rooms"", on_delete = models.CASCADE)
    house = models.ForeignKey(House, related_name = ""rooms"", on_delete = models.CASCADE)

class Window(models.Model):
    story = models.ForeignKey(Story, related_name = ""windows"", on_delete = models.CASCADE)
    room = models.ForeignKey(Room, related_name = ""windows"", on_delete = models.CASCADE)

House.objects.prefetch_related(
    ""rooms__windows"",
    ""stories__rooms__windows""
).all()
}}}

This results in something like this:

{{{
SELECT ••• FROM ""houses_house""
SELECT ••• FROM ""houses_room"" WHERE ""houses_room"".""house_id"" IN (1)
SELECT ••• FROM ""houses_window"" WHERE ""houses_window"".""room_id"" IN (1) # DUPLICATE
SELECT ••• FROM ""houses_story"" WHERE ""houses_story"".""house_id"" IN (1)
SELECT ••• FROM ""houses_room"" WHERE ""houses_room"".""story_id"" IN (1)
SELECT ••• FROM ""houses_window"" WHERE ""houses_window"".""room_id"" IN (1) # DUPLICATE
}}}

The same behavior occurs when the last relation in the chain is one-to-one."	Cleanup/optimization	closed	Database layer (models, ORM)	5.1	Normal	wontfix			Unreviewed	0	0	0	0	0	0