A left outer reverse select_related in Django?

Question

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

Solution 1

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.

Solution 2

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>

Comments

[augustomen]

Not quite; as far as I'm concerned, select_related() does no reverse lookup, it only looks forward.

[Bernhard Vallant]

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...

[Andi Albrecht]

According to the docs select_related() can do reverse lookups starting with Django 1.2, but only for OneToOneFields.

[gsharma]

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?