I was looking for an easy way to define extra outer joins. It
turned out that it's easy to extend the QuerySet.extra method
with a 'joins' argument used like this:

notes = Note.objects.select_related().extra(
   joins=['''left outer join %s rel1 on
             rel1.id=%s.release_beginn_id'''
          % (Release._meta.db_table, Note._meta.db_table),
          '''left outer join %s rel2  on
             rel2.id=%s.release_behoben_id'''
          % (Release._meta.db_table, Note._meta.db_table)],
   where=['''((%(notes_table)s.release_beginn_id is null or
                   rel1.sort_value <= %(sort_value)d)
              and (%(notes_table)s.release_behoben_id is null or
                   rel2.sort_value > %(sort_value)d))'''
                 % {'sort_value': version, 'notes_table':
                       Note._meta.db_table}])

(I left out the db name quoting since it doesn't contribute much
to this example.)

The patch is surprisingly small:

http://code.djangoproject.com/attachment/ticket/2922/extra_joins.diff

I guess this is currently under a freeze. I just wanted to bring
it up for discussion. If there's serious interest, I can flesh it
out (docs, testcases) later when the winter^H^H^H^H^H^H^H freeze
is over.

Michael

When you invoke get_sql on a Q object, it returns a tuple, the first
argument of which is 'joins', a sorted dictionary that describes the
joined table name, join type, and join condition.

It's not a heavily advertised feature, so there isn't much
documentation; if you want an example, line 840 of query.py shows the
addition of a join to the sql results.

Using this approach also means that you can keep using Django query
syntax for most of your query, rather than just inserting a block of
SQL.

Yours,
Russ Magee