Outer joins as extras()
Alan Green
Has anybody found a way to add an table to query with an outer join? I
tried the Obvious Thing:
queryset = Reader.objects.all().extras(
select = {'other_name': 't.name'},
tables = ["left outer join other_table t on t.id = hr_reader.other_id"]))
That results in the database complaining about an extra comma, because
the tables list is simply put into the generated query's FROM cause
with commas between each item.
I'm currently working around this by creating a database VIEW that
performs the outer join, then adding that view to the extras. It
works, but it's ugly.
Anybody got a different way to do it?
Alan.
--
Alan Green
al...@bright-green.com - http://bright-green.com
Russell Keith-Magee
Hi,
Has anybody found a way to add an table to query with an outer join?
Anybody got a different way to do it?
It isn't really a documented feature, and I haven't worked through the details (so you will need to do a little code spelunking by yourself) but you might get some traction playing around with Q objects in django/db/models/query.py.
Although there is a base Q object, the only contractual obligation of that object is that it has a get_sql method that returns a tuple (joins, where, params). The 'joins' member is itself a list of tuples, each member being of the form (alias, (table, join_type, condition)).
Line 462 of _get_sql_clause() on QuerySet shows how this tuple is then composed into an SQL query when a QuerySet is evaluated.
So; if you wanted to set up an outer join, you could write a Q object (or Q-like object) that has a get_sql that returns OUTER JOIN as the join type, and pack it into a filter/exclude as you would any other Q object.
Like I said - I haven't tried this; if it breaks, you get to keep every single one of the pieces :-)
Yours,
Russ Magee %-)
Alan Green
> On 8/22/06, Alan Green <alan....@gmail.com> wrote:
> > Has anybody found a way to add an table to query with an outer join?
> It isn't really a documented feature, and I haven't worked through the
> details (so you will need to do a little code spelunking by yourself) but
> you might get some traction playing around with Q objects in
> django/db/models/query.py.
Thanks. This worked great. I made this class:
from django.db.models import Q
from django.utils.datastructures import SortedDict
class QLeftOuterJoin(Q):
def __init__(self, alias, table, where):
self.alias, self.table, self.where = alias, table, where
def get_sql(self, opts):
joins = SortedDict()
joins[self.alias] = (self.table, 'LEFT OUTER JOIN', self.where)
return (joins, [], [])
As you suggested, this can then be used as an arg to filter() like any
other Q object:
queryset = queryset.filter(QLeftOuterJoin('rmr', "hr_reader_most_recent",
"rmr.reader_id = hr_reader.id"))
> Although there is a base Q object, the only contractual obligation of that
> object is that it has a get_sql method that returns a tuple (joins, where,
> params). The 'joins' member is itself a list of tuples, each member being of
> the form (alias, (table, join_type, condition)).
I ended up having to extend Q anyway. The QAnd and QOr classes check
the type of with isinstance(), and they were raising a TypeError
exception.
> Like I said - I haven't tried this; if it breaks, you get to keep every
> single one of the pieces :-)
And if anyone uses the above QLeftOuterJoin class, and it breaks their
program, they get to keep all the pieces too.
Thanks again - this has helped me tidy up a nasty, mess patch in my code.
Cheers,