Full outer join in django

Question

How can I create a query for a full outer join across a M2M relationchip using the django QuerySet API?

It that is not supported, some hint about creating my own manager to do this would be welcome.

Edited to add: @S.Lott: Thanks for the enlightenment. The need for the OUTER JOIN comes from the application. It has to generate a report showing the data entered, even if it still incomplete. I was not aware of the fact that the result would be a new class/model. Your hints will help me quite a bit.

Answer 1

Django doesn't support "joins" in the usual SQL sense -- it supports object navigation.

Note that a relational join (inner or outer) creates a new "class" of entities. One that doesn't have a definition in Django. So there's no proper "result set" since there's no class definition for the things you get back. The best you can do is define a tuple which will be packed with None's for missing combinations.

A left (or right) outer join looks like this. It creates two disjoint subsets, those who have an associated set of related entities, and those who don't.

for obj in Model1.objects.all():
    if obj.model2_set().count() == 0:
        # process (obj, None) -- no Model2 association
    else:
        for obj2 in obj.model2_set.all():
            # process (obj, obj2) -- the "inner join" result

A "Full" outer join is a union of the remaining items that have no relationships.

for obj2 in Model2.objects.all():
    if obj2.model1_set().count() == 0:
        # process (None, obj2) -- no Model1 association

The issue is always, what processing are you doing with this weird collection of three different subsets of objects?

The point of an object database is to focus the processing on the object and it's associated objects.

The peculiar collection called a "relational join" is never in the original object model. It's a new class of objects built from two (or more) original objects.

Worse, outer joins create a collection with multiple subclasses (inner join, left outer join and right outer join). What does that collection of things mean?

Wait, it can get worse. If the processing includes checks for the missing attributes (i.e. if someObj.anObj2attribute is None: we're essentially looking for Model1 items with no Model2 object associated. Ummm... why did we put those in the outer join, only to filter them using an if statement? Why not just do separate queries amd process each subset properly?

---

Edit: When you're showing "incomplete" status, it isn't an outer-join at all. It's much simpler. You need to create one (or two) separate collections in your view function for your template to display.

First, you should use status codes, not the presence or absence of a foreign key. Optional foreign keys don't have "reasons" -- they're either there or not there. A status code can provide useful shades of meaning ("incomplete", "in error", "broken", "not applicable", "to be deleted", etc.)

errorList1 = Model1.objects.filter( status="Incomplete" )
errorList2 = Model2.objects.filter( status="Incomplete" )

These two are the two non-join parts of a full outer join. You can then display these two error lists in your template with appropriate column titles and status codes and everything.

You can even put them into a single table to mimic the old full outer join report people used to see

<table>
    <tr><th>Model1</th><th>Model2</th></tr>
    {% for e1 in errorList1 %}
    <tr><td>e1</td><td>NULL</td></tr>
    {% endfor %}
    {% for e2 in errorList2 %}
    <tr><td>NULL</td><td>e2</td></tr>
    {% endfor %}
</table>

Looks like a full outer join report. Without the full outer join.

Answer 2

Colin, one of the guys I work with, wrote a post awhile back about doing custom joins in Django:

http://www.caktusgroup.com/blog/2009/09/28/custom-joins-with-djangos-queryjoin/

You might be able to find something useful there!