OK sorry, i didn't look carefully enough. when you use a scalar
subquery, you shouldn't access the "c" attribute on it. I hadn't
really realized that and maybe i should add an exception for that.
when you access the "c" attribute, you're treating it like another
relation to be selected from, so it gets stuck into the from clause.
but here, its really just a column expression; so you don't join
against one of the subqueries' columns, the subquery IS the thing to
be compared against. so heres the full testcase:
"""
create table node(id integer, parent_id integer, type_id integer);
insert into node(1,NULL,1);
insert into node values(1,NULL,1);
insert into node values(2,1,1);
insert into node values(3,1,2);
insert into node values(4,1,1);
SELECT node.id, node.parent_id, node.type_id
FROM node
WHERE node.id = (SELECT max(n1.id) FROM node AS n1 WHERE n1.type_id =
node.type_id);
"""
from sqlalchemy import *
meta = MetaData()
node_table = Table('nodes', meta,
Column('id', Integer),
Column('parent_id', Integer),
Column('type_id', Integer),
)
meta.bind = create_engine('sqlite://', echo=True)
meta.create_all()
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=3, parent_id=1, type_id=2)
node_table.insert().execute(id=4, parent_id=1, type_id=1)
n1 = node_table.alias('n1')
sub_query = select([func.max(n1.c.id)],
(node_table.c.type_id==n1.c.type_id), scalar=True)
print
node_table.select(node_table.c.id==sub_query).execute().fetchall()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
