Django - reverse lookups

Question

For example, I have these models:

class Person(models.Model):
    name = models.CharField(max_length=20)
    employer = models.CharField(max_length=20)

class Car(models.Model):
    person = models.ForeignKey(Person)
    name = models.CharField(max_length=10)
    model = models.CharField(max_length=10)
    ...

I want to get all people who own some specific car:

people = Person.objects.filter(car__name="Toyota")

Now I want to write these people out with details about their own car. I can do this:

for person in people:
   ...
   cars = person.car_set.filter(name="Toyota")
   ...

But it hit the database again. How can I avoid this? Is there any way to do this simpler?

score 3 · Accepted Answer · answered Jul 10 '11 at 04:49

First select the car and the related person when the car name

cars = Car.objects.select_related("person").filter(name="Toyota").order_by("person")

Now you have all the cars whose name is toyota along with the person for that car, ordered_by person.

Now use the python itertools.groupby to group this list for each person

from itertools import groupby
for k, g in groupby(cars, lambda x: x.person):
       person = k
       cars = list(g)

Now in that iteration you have the person and his cars (whose name is "toyota"). You'll notice that there is only single query that occurs, and then the following operations execute on the cached information.

One side effect of using this technique is that if you have person who has no cars, you will not see that in that query. This is because the query is initiated over the Domain of Cars. — arustgi, Nov 15 '11 at 06:25

score 0 · Answer 2 · answered Jul 08 '11 at 17:54

0

Check out select_related(), I've used it before to turn many small queries that span multiple models into one large query: https://docs.djangoproject.com/en/dev/ref/models/querysets/#select-related

It works by pre-populating the QuerySet, so your access to car_set will already be there and won't result in a new query.

answered Jul 08 '11 at 17:54

kcbanner

4,050
1
25
18

Even if I need to filter it again? I don't want every cars, but only Toyotas. – yetty Jul 08 '11 at 17:58
If you re-use the same `QuerySet`, it may work. Check out: https://docs.djangoproject.com/en/dev/topics/db/queries/#caching-and-querysets for more details on `QuerySet` caching. – kcbanner Jul 08 '11 at 18:04

score 0 · Answer 3 · answered Jul 08 '11 at 18:04

I don't believe there is anyway to avoid the additional database hits. If you're concerned about making too many queries, you can try something like this:

from collections import defaultdict

cars = Car.objects.filter(**kwargs).selected_related('person')
owners = defaultdict(list)

for car in cars:
    owners[car.person].append(car)

This should only be one query that selects all the relevant cars and the data about their related person

Django - reverse lookups

3 Answers3