Django: getting the list of related records for a list of objects

Question

I have two models related one-to many:

class Person(models.Model):
    name      = models.CharField(max_length=255);
    surname   = models.CharField(max_length=255);     
    age       = models.IntegerField(); 


class Dog(models.Model):
    name      = models.CharField(max_length=255);
    owner     = models.ForeignKey('Person');

I want to output a list of persons and, below each person a list of dogs he has. Here's how I can do it:

in view:

persons = Person.objects.all()[0:100];

in template:

{% for p in persons %}
    {{ p.name }} has dogs:<br />
    {% for d in persons.dog_set.all %}
        - {{ d.name }}<br />
    {% endfor %}
{% endfor %}

But if I do it like that, Django will execute 101 SQL queries which is very inefficient.

I tried to make a custom manager, which will get all the persons, then all the dogs and links them in python, but then I can't use paginator (my another question: Django: Paginator + raw SQL query ) and it looks quite ugly.

Is there a more graceful way doing this?

UPDATE

Solution based on blog entry by @Daniel Roseman

all_objects = Person.objects.select_related().all();

paginator = Paginator(all_objects, 100);

try:
    page = int(request.GET.get('page', '1'))
except ValueError:
    page = 1

try:
    current_page = paginator.page(page)
except (EmptyPage, InvalidPage):
    current_page = paginator.page(paginator.num_pages)

person_list = dict([(obj.id, obj) for obj in current_page.object_list])
id_list = [obj.id for obj in current_page.object_list]

objects = Dog.objects.filter(owner__id__in=id_list)

relation_dict = {}
for obj in objects:
    relation_dict.setdefault(obj.owner_id, []).append(obj)

for id, related_items in relation_dict.items():
    person_list[id].dogs = related_items

See my blog (sorry for the plug) where I describe an efficient way to do this. — Daniel Roseman
– Daniel Roseman, Commented Mar 28, 2010 at 11:44

Bob · Accepted Answer · 2018-04-23 17:34:16Z

3

With the newer versions of Django (>=1.7 or maybe even some older ones) you can just replace your

persons = Person.objects.all()[0:100]

with

persons = Person.objects.prefetch_related('dog_set').all()[0:100]

and that's it, no need to keep using the old workaround if you are on Django>=1.7

the official docs on prefetch_related are here: https://docs.djangoproject.com/en/2.0/ref/models/querysets/#prefetch-related

answered Apr 23, 2018 at 17:34

Bob

6,2526 gold badges41 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django: getting the list of related records for a list of objects

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related