The following example is very contrived, but it's a very simplified
version of what I am actually trying to do.
Basically I'm trying to join with a derived table, that itself is a
self join.
Can someone help me figure out what I am doing wrong with this
example?
Thanks
#create meta
#create session
import sqlalchemy
table = sqlalchemy.Table (
'test_table', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('foo', sqlalchemy.Integer),
mysql_engine='InnoDB'
)
table2 = sqlalchemy.Table (
'test_table_2', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('bar', sqlalchemy.Integer),
mysql_engine='InnoDB'
)
class Test(object):
pass
class Test2(object):
pass
mapper =sqlalchemy.orm.mapper(Test, table)
mapper =sqlalchemy.orm.mapper(Test2, table2)
meta.create_all()
for i in range(3):
row1 = Test()
row1.foo = i
session.add(row1)
row2 = Test2()
row2.bar = i
session.add(row2)
session.flush()
inside = table.alias('inside')
outside = inside.join(table, table.c.foo < 2)
s = outside.select(use_labels=True).alias('outside')
j = table2.join(s, table2.c.bar == outside.c.inside_foo)
s = j.select(use_labels=True)
rows = session.query(Test2).add_entity(Test, alias=inside).add_entity
(Test, alias=outside).from_statement(s).all()
for row in rows:
print row
At this point, i get this exception:
sqlalchemy.exc.OperationalError: (OperationalError) (1054, "Unknown
column 'inside.foo' in 'on clause'") u'SELECT test_table_2.id AS
test_table_2_id, test_table_2.bar AS test_table_2_bar,
outside.inside_id AS outside_inside_id, outside.inside_foo AS
outside_inside_foo, outside.test_table_id AS outside_test_table_id,
outside.test_table_foo AS outside_test_table_foo \nFROM test_table_2
INNER JOIN (SELECT inside.id AS inside_id, inside.foo AS inside_foo,
test_table.id AS test_table_id, test_table.foo AS test_table_foo
\nFROM test_table AS inside INNER JOIN test_table ON test_table.foo <
%s) AS outside ON test_table_2.bar = inside.foo' [2]
I believe that it has to do with the fact that I have two aliases,
and that the query is selecting outside.inside_foo as
outside_inside_foo (a chained alias), but that the mapper is not aware
of the chaining. Is there a way to accomplish what I want to do here?
Thanks,
David
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
