[ACCEPTED]-Full outer join in django-outer-join

Accepted answer
Score: 17

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

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

A left (or right) outer 44 join looks like this. It creates two disjoint 43 subsets, those who have an associated set 42 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
        for obj2 in obj.model2_set.all():
            # process (obj, obj2) -- the "inner join" result

A 41 "Full" outer join is a union of the remaining 40 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 39 is always, what processing are you doing 38 with this weird collection of three different 37 subsets of objects?

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

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

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

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

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

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

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

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

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

    {% for e1 in errorList1 %}
    {% endfor %}
    {% for e2 in errorList2 %}
    {% endfor %}

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

Score: 1

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


You might be able to find something 1 useful there!

More Related questions