Excelent ! It works perfectly !!
Thank you very much Michael. I was going crazy trying to figure how to
move
subquery to the from clause.
On Aug 9, 8:06 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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 [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
-~----------~----~----~----~------~----~------~--~---
