select_related with reverse foreign keys

Question

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 using Person.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 just position__reports_to__person to the select_related, but that doesn't seem to change the query. Is this what prefetch_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(
        )

Shagbark

probably a typo, but it should be get_queryset(), not get_query_set. – Sciurine

@Sciurine it was get_query_set in Django 1.5. – Shagbark

Answer 1

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')

Marcellamarcelle