defining outer joins

[Michael Radziej]

Hi,

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

[Russell Keith-Magee]

On 10/17/06, Michael Radziej <m...@noris.de> wrote:
>
> 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.

You can already specify the join type using Q objects.

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