On Mar 5, 2008, at 6:43 PM, kris wrote:
>
> The parser will see expressions for A first, B, and then C,
> I would like to do something like the following in sequence but not
> necessarily at the same time.
>
> q = session.query(A).filter (A.name == 'boo')
> q = session.query(B).filter (B.parent_id == q && B.value == 1)
> q = session.query (C).filter (C.parent_id == q)
>
> which would generate something like:
>
> select id from ctable where ctable.parent_id in
> ( select id from btable where btable.value =1 and
> btable.parent_id in
> ( select id from atable where atable.name = 'boo'))
>
> ##
>
The Query doesn't serve as a clause construct in itself at this time.
You can get at its ClauseElement by calling its compile() method, but
right now Query still only returns full object instances with optional
additional columns; not arbitrary columns alone. We have a branch in
SVN which allows this and it'll be merged soon.
However, as you'll see we have better ways to do what youre trying to
do. You can create the exact query you want above using SQL
expressions - note you can still use the classes for filter criterion
without the need for dealing with the Table objects:
s = select([A.id]).where(A.name=='boo')
s = select([B.id]).where(B.value==1).where(B.parent_id.in_(s))
session.query(C).filter(C.parent_id.in_(s))
Now above, the strategy of saying "foo IN (select something)" is
actually not the best approach from a SQL perspective - an EXISTS with
a correlated WHERE criterion is generally more efficient and less
error prone. The explicit version of that looks like:
s = exists(['*']).where(and_(B.parent_id==A.id, A.name=='boo'))
s = exists(['*']).where(and_(B.value==1, C.parent_id==B.id, s))
session.query(C).filter(s)
Then to shrink down the above, we can use the any() and has()
operators to generate the EXISTS clauses for us. Assuming your
mappers look like:
mapper(A, atable)
mapper(B, btable, properties={'a':relation(A)})
mapper(C, ctable, properties={'b':relation(B)})
you can generate the above query just like this:
session.query(C).filter(C.b.has(and_(B.a.has(A.name=='boo'),
B.value==1)))
and that above generates for you a reasonably efficient query with a
minimum amount of verbosity.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
