On Jul 16, 7:33 pm, Conor <conor.edward.da...@gmail.com> wrote:
> On 07/16/2010 09:53 AM, dr wrote:
>
>
>
> > Michael,
>
> > I have implemented what you suggested and below is a comparison of the
> > results:
>
> > ***************
> > 2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
> > BEGIN
> > 2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
> > SELECT tbl1.id, tbl1.text, tbl2.id AS parent
> > FROM nsm AS tbl1
> > LEFT OUTER JOIN nsm AS tbl2
> > ON tbl2.lft = (SELECT MAX(tbl3.lft)
>
> > FROM nsm AS tbl3
>
> > WHERE tbl1.lft > tbl3.lft
>
> > AND tbl1.lft < tbl3.rgt)
> > 2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
> > ()
> > (1, 'Albert', None)
> > (2, 'Bert', 1)
> > (3, 'Chuck', 1)
> > (4, 'Donna', 3)
> > (5, 'Eddie', 3)
> > (6, 'Fred', 3)
> > ***************
> > 2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
> > SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
> > parent
> > FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft = (SELECT
> > max(nsm_3.lft) AS max_left
> > FROM nsm AS nsm_3, nsm AS nsm_1
> > WHERE nsm_1.lft < nsm_3.lft AND nsm_1.lft < nsm_3.rgt)
> > 2010-07-16 15:44:31,536 INFO sqlalchemy.engine.base.Engine.0x...7910
> > ()
> > (1, 'Albert', 6)
> > (2, 'Bert', 6)
> > (3, 'Chuck', 6)
> > (4, 'Donna', 6)
> > (5, 'Eddie', 6)
> > (6, 'Fred', 6)
> > ***************
> > Comparing the raw sql (correct) and query/subquery results you will
> > notice that the query/subquery does not return the correct parent id
>
> > Any further ideas about why this is producing this result?
>
> You need to force SQLAlchemy to correlate the subquery to the outer
> query. This will ensure that the subquery uses the outer query's tbl1
> instead of trying to generate a new tbl1 FROM clause.
>
> subq = session.query(func.max(tbl3.left).label('max_left'))
> subq = subq.filter(tbl1.left > tbl3.left)
> subq = subq.filter(tbl1.left < tbl3.right)
> # NEW: Add correlation
> subq = subq.correlate(tbl1)
> subq = subq.subquery().as_scalar()
>
> q = session.query(tbl1.id, tbl1.text, tbl2.id.label('parent'))
> q = q.outerjoin((tbl2,tbl2.left==subq))
>
> -Conor
Conor,
Thank you that was the solution. I guess it is back to the
documentation for me.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
