[sqlalchemy] Re: Simple outer join problem when using select object - Michael Bayer - com.googlegroups.sqlalchemy

4 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: Simple outer join pr...

From	Sent On	Attachments
Juan Germano	22 May 2007 07:05
Michael Bayer	22 May 2007 07:46
Michael Bayer	22 May 2007 09:50
Juan Ignacio Germano	22 May 2007 10:41

Subject:	[sqlalchemy] Re: Simple outer join problem when using select object
From:	Michael Bayer (mike...@zzzcomputing.com)
Date:	05/22/2007 07:46:42 AM
List:	com.googlegroups.sqlalchemy

I will work on it, but heres a comment from the eager loading code that creates the OUTER JOIN:

# if the mapper is against a select statement or something, we cant handle that at the # same time as a custom FROM clause right now.

and you have exactly that here. so until I figure out how to expand eager loading's intelligence on this issue (which may take only half an hour), either forego the eager load or create the full query explicitly.

On May 22, 2007, at 10:06 AM, Juan Germano wrote:

a_table = Table('a', metadata, Column('id', Integer, primary_key = True), Column('somedata', Unicode), )

b_table = Table('b', metadata, Column('id', Integer, primary_key = True), Column('somedatainb', Unicode), Column('modtime', DateTime, default = datetime.utcnow()), Column('a_id', Integer, ForeignKey('a.id')), )

c_table = Table('c', metadata, Column('id', Integer, primary_key = True), Column('somedatainc', Unicode), Column('b_id', Integer, ForeignKey('b.id')), )

class A(object): pass class B(object): pass class C(object): pass

s = select([b_table, func.now().op('-') (b_table.c.modtime).label('dayssince')]).alias('b_table_b')

assign_mapper(session.context, A, a_table, properties = { 'bs':relation(B, lazy = None) } )

assign_mapper(session.context, B, s, properties = { 'a':relation(A, lazy = None), 'cs':relation(C, lazy = None), } )

assign_mapper(session.context, C, c_table, properties = { 'b':relation(B, lazy = None) } )

The query should be this one:

query = session.query(B) q2 = query.options(eagerload('a')) oj = q2.outerjoin('cs') oj2 = oj.filter(c_table.c.id == None) res = list(oj2)