Nested LEFT OUTER JOIN

[Dan]

Hello everyone,

I'm trying to do a slightly complex SQL operation within django and
I'm asking for some help.

Here's simplified examples of the relevant models:

class Partner(models.Model):
name = models.CharField(max_length=120)

class Persona(models.Model):
name = models.CharField(max_length=120)
partners = models.ManyToManyField(Partner)


And here is the SQL I want to execute:

select * from
(iuser_persona
left join
(select ipp.persona_id, pp.id as partner_id from
iuser_persona_partners as ipp
left join
partners_partner as pp
on ipp.partner_id=pp.id
) as pids
on iuser_persona.id=pids.persona_id
)


I realize that this is a bit too complex for the default django ORM
and I'm okay with that, but I would really like to get a QuerySet back
instead of a list of IDs with which to use .filter(id__in=lst) (the
reason being that the ID list won't be guaranteed unique so just doing
an IN query won't get me all the results... it's why I need a LOUTER
JOIN in the first place). So I thought Q objects would be the way to
go, but even with direct access to the Query I'm still limited to
alias_map, join_map, etc., I can't figure out a way to tell the ORM to
do a nested LOUTER JOIN.

Any tips?

[Plamen Dragozov]

Hi,
I'm currently working on something similar and this is what I have at
the moment. I extend the django.db.models.sql.query.Query class and
overwrite the get_from_clause(...) method. Then I implement my own
django.db.models.Manager and overwrite the get_query_set(...) and the
extra(...) methods to use the new Query class. It's not exactly what you
are looking for, it's still work in progress and I haven't tested it
much, but it could be a good start (Oh and I'm using 1.0-alpha-SVN-8107).

from django.db.models.sql import query
from django.db.models.sql.where import WhereNode

class ExtraJoinsQuery(query.Query):
"""Supports explicit left outer joins
"""
def __init__(self, model, connection, where=WhereNode, joins = None):
super(ExtraJoinsQuery, self).__init__(model, connection, where)
self.extra_joins = joins or []

def clone(self, klass=None, **kwargs):
obj = super(ExtraJoinsQuery, self).clone(klass, **kwargs)
obj.extra_joins = self.extra_joins[:]
return obj

def get_from_clause(self):
"""
Returns a list of strings that are joined together to go after the
"FROM" part of the query, as well as a list any extra parameters
that
need to be included. Sub-classes, can override this to create a
from-clause via a "select", for example (e.g. CountQuery).

This should only be called after any SQL construction methods that
might change the tables we need. This means the select columns and
ordering must be done first.
"""
result = []
qn = self.quote_name_unless_alias
qn2 = self.connection.ops.quote_name
for join in self.extra_joins:
name, alias, join_type, lhs, lhs_col, col = join
alias_str = (alias != name and ' %s' % alias or '')
result.append('%s %s%s ON (%s.%s = %s.%s)'
% (join_type, qn(name), alias_str, qn(lhs),
qn2(lhs_col), qn(alias), qn2(col)))
super_from = super(ExtraJoinsQuery, self).get_from_clause()
head = [super_from[0][0]]#the main table
add = False #skip the first
for fc in super_from[0]:
if add:
if fc[0] == ",":
head.append(fc) #tables go first
else:
result.append(fc)#joins go after
else:
add = True
head.extend(result)
return head, super_from[1]

class ExtraManager(djangodb.Manager):
def get_query_set(self):
"""Returns a new QuerySet object. Subclasses can override this
method
to easily customize the behavior of the Manager.
"""
return djangodb.query.QuerySet(self.model,
dbutil.ExtraJoinsQuery(self.model, connection))

def extra(self, joins = None, *args, **kwargs):
qs = self.get_query_set().extra(*args, **kwargs)
if(joins):
qs.query.extra_joins.extend(joins)
return qs

Best,
Plamen Dragozov

[Dan]

Hi Plamen,

Thanks a bunch for your reply. I'm in the process of modifying your
example to suit my needs. I will post my final solution.


Thanks,
Dan