I'm using version 0.3.9 with PostgresSQL 8.1 .
Because postgres needs aliased subqueries i changed the example code
you sent to:
n1 = node_table.alias('n1')
sub_query = select([func.max(node_table.c.id).label('max_id')],
(node_table.c.type_id==n1.c.type_id), scalar=True)
sub_query.correlate(n1)
sub_query = sub_query.alias('sub_query')
n1.select(n1.c.id==sub_query.c.max_id).execute().fetchall()
The problem now is that is not possible to have the sub_query at FROM
level:
SQLError: (ProgrammingError) subquery in FROM may not refer to other
relations of same query level
'SELECT n1.id, n1.parent_id, n1.type_id \nFROM node AS n1, (SELECT
max(node.id) AS max_id \nFROM node \nWHERE node.type_id = n1.type_id)
AS sub_query \nWHERE n1.id = sub_query.max_id'
so i'm going to see if its possible to move sub_query to WHERE level.
If i find the solution i'll post it.
Thank you very much for replying so quickly !
On Aug 9, 5:49 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> if youre on 0.3, you can explicitly correlate to n1 by saying:
>
> myselect.correlate(n1)
>
> also in 0.3, it would probably help to say "scalar=True" in your
> select() statement (and you dont need the alias):
>
> n1 = node_table.alias('n1')
> sub_query = select([func.max(node_table.c.id).label('max_id')],
> (node_table.c.type_id==n1.c.type_id), scalar=True)
> sub_query.correlate(n1)
> n1.select(n1.c.id==sub_query.c.max_id).execute().fetchall()
>
> if on 0.4, the whole thing is easier, id write it like this:
>
> n1 = node_table.alias('n1')
> sub_query = select([func.max(node_table.c.id).label('max_id')],
> (node_table.c.type_id==n1.c.type_id)).as_scalar().correlate(n1)
> n1.select(n1.c.id==sub_query.c.max_id).execute().fetchall()
>
> however.....it *should* be correlating automatically anyway...some
> combination of the "alias" and the no "scalar" might be affecting
> that in the 0.3 version...what version are you using ?
>
> On Aug 9, 2007, at 12:30 PM, Jeronimo wrote:
>
>
>
> > Greetengs, i tried to solve the problem using different approaches but
> > none of them worked.
> > Here are the examples using both, plain SQL and SQLALchemy methods.
>
> > * Using plain SQL:
>
> > 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);
>
> > Results:
>
> > id | parent_id | type_id
> > ----+-----------+---------
> > 3 | 1 | 2
> > 4 | 1 | 1
> > (2 rows)
>
> > * Using SQLALchemy:
>
> > class Node(BaseModel): pass
> > node_table = Table('node', metadata, Column('id', Integer),
> > Column('parent_id', Integer), Column('type_id', Integer))
> > assign_mapper(session.context, Node, node_table)
>
> > n1 = node_table.alias('n1')
> > sub_query = select([func.max(node_table.c.id).label('max_id')],
> > (node_table.c.type_id==n1.c.type_id)).alias('sub_query')
> > n1.select(n1.c.id==sub_query.c.max_id).execute().fetchall()
>
> > Results:
>
> > [(4, 1, 1)]
>
> > It seems that sub_query is not using the parent query field
> > definitions, and instead is redeclaring node_table.
> > Does someone knows whats happening ? Thanks in advance !
>
> > Regards !
> > Jeronimo
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
