select_related with reverse foreign keys
Yes, that is what prefetch_related()
is for. It will require an additional query, but the idea is that it will get all of the related information at once, instead of once per Person
.
In your case:
qs.select_related('position__report_to')
.prefetch_related('position__report_to__person_set')
should require two queries, regardless of the number of Persons
in the original query set.
Compare this example from the documentation:
>>> Restaurant.objects.select_related('best_pizza')
.prefetch_related('best_pizza__toppings')
Paul Tomblin
Java/C++/Perl/Python/Javascript/C# programmer on Linux/Unix/macOS/Windows. 25+ years experience. Still learning. http://www.linkedin.com/in/paultomblin
Updated on February 10, 2020Comments
-
Paul Tomblin over 4 years
I have two Models in Django. The first has the hierarchy of what job functions (positions) report to which other positions, and the second is people and what job function they hold.
class PositionHierarchy(model.Model): pcn = models.CharField(max_length=50) title = models.CharField(max_length=100) level = models.CharField(max_length=25) report_to = models.ForeignKey('PositionHierachy', null=True) class Person(model.Model): first_name = models.CharField(max_length=50) last_name = models.CharField(max_length=50) ... position = models.ForeignKey(PositionHierarchy)
When I have a Person record and I want to find the person's manager, I have to do
manager = person.position.report_to.person_set.all()[0] # Can't use .first() because we haven't upgraded to 1.6 yet
If I'm getting people with a
QuerySet
, I can join (and avoid a second trip to the database) with position and report_to usingPerson.objects.select_related('position', 'position__reports_to').filter(...)
, but is there any way to avoid making another trip to the database to get the person_set? I tried adding'position__reports_to__person_set'
or justposition__reports_to__person
to theselect_related
, but that doesn't seem to change the query. Is this whatprefetch_related
is for?I'd like to make a custom manager so that when I do a query to get Person records, I also get their PositionHeirarchy and their manager's Person record without more round trips to the database. This is what I have so far:
class PersonWithManagerManager(models.Manager): def get_query_set(self): qs = super(PersonWithManagerManager, self).get_query_set() return qs.select_related( 'position', 'position__reports_to', ).prefetch_related( )