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
--
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.
