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

Osseous

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

A downside of the regroup approach is that it won't get Parents that aren't there – Anthracosilicosis

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

Utilitarianism

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? – Interpretive

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

Osseous

Answer 3

I think you are looking for select_related()

Mistrust

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

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

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