36

Imagine the following model:

class Parent(Model):
    ...

class Child(Model)
    father = ForeignKey(Parent)
    ...

Some parents have children, others do not (they're not parents in the real meaning, it's just a fictional name).

I would like to make the following query: I want to list all the Parents, and if they have children, bring me the children too. That would be the equivalent of a left outer join to Child table, that is:

select * from app_parent left join app_child on child_father_id=parent_id

This way, when I invoke Parent.child_set in my template, I won't hit the database a gazillion times. Is there a way to do that? Thanks

augustomen
  • 8,977
  • 3
  • 43
  • 63
  • 1
    I decided I shouldn't use this approach; there's really no support. It would seem the best approach is to select from the childmost table and then, if needed, use the "regroup" templatetag or the set() function in the parent fields, depending on the case. – augustomen Jul 19 '10 at 14:35
  • 1
    A downside of the regroup approach is that it won't get Parents that aren't there – Ben G Feb 16 '12 at 22:05

3 Answers3

25

Starting from Django 1.4 prefetch_related does what you want.

Parent.objects.prefetch_related('child_set')

Related(!) django docs : https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related.

Gautier Hayoun
  • 2,922
  • 24
  • 17
  • 3
    I don't think it is the same thing as OP asked. I just ran select_prefetch and it actually runs 2 queries: 1. select * from parents; 2. select * from child where parent_id IN (-bunch-of-comma-separated-ids-from-query-1-go-here-) Is this how it is supposed to work or am I doing something wrong? – gsharma Dec 20 '12 at 00:50
7

In this case, I think the best thing to do is list the children, then get the parent from them, like this:

children = Child.objects.filter(...).select_related('parent').order_by('parent')

Then in the template, possibly use a regroup (note the order_by above):

{% regroup children by parent as parents %}
<ul>
{% for parent in parents %}
    <li>{{ parent.grouper }}
    <ul>
    {% for child in parents.list %}
    ...
    {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>
augustomen
  • 8,977
  • 3
  • 43
  • 63
-7

I think you are looking for select_related()

cethegeek
  • 6,286
  • 35
  • 42
  • 3
    Not quite; as far as I'm concerned, select_related() does no reverse lookup, it only looks forward. – augustomen Jun 04 '10 at 18:12
  • Are you sure about that? I looked at the Django documentation and it says it does for 1:1 relations, but not sure about ForeignKey-relations... – Bernhard Vallant Jun 04 '10 at 19:51
  • 2
    According to the docs select_related() can do reverse lookups starting with Django 1.2, but only for OneToOneFields. – Andi Albrecht Mar 09 '11 at 10:39