I cant manage to make sub_select appear at WHERE level, it always
appears at FROM level.
The SQL statement needs to select one node for each type, and each
node must be the one
with the max id in that type.
Is it possible to make this statement using SQLAlchemy ?
On Aug 9, 6:11 pm, Jeronimo <[EMAIL PROTECTED]> wrote:
> 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 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
-~----------~----~----~----~------~----~------~--~---
