On 16 July, 15:15, Michael Bayer <[email protected]> wrote:
> On Jul 16, 2010, at 4:55 AM, dr wrote:
>
>
>
> > I have heirachial data stored as a nested set model and I am trying to
> > retreive the data as a adjacent list model to supply to a tree widget
> > in an application. I have been playing around with and modifying the
> > nested set example provided with sqlalchemy to develop the queries. I
> > am using python 3.1.2 and sqlalchemy 0.6.2 on windows xp
>
> > I have created alisases for the table with the following:
> > tbl1 = aliased(NSM)
> > tbl2 = aliased(NSM)
> > tbl3 = aliased(NSM)
>
> > I am trying to implement the following query:
> > rawsql = """
> > 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)"""
>
> > executing the raw SQL query as:
> > raw_query = session.query(tbl1.id, tbl1.text,
> > tbl2.id.label('parent')).from_statement(rawsql)
> > rset = raw_sql.all()
>
> > works fine and the adjacent list model form is returned.
>
> > However, when I try to implement it with subquery in sqlalchemy in the
> > following form:
>
> > sub_query =
> > session.query(func.max(tbl3.left).label('max_left')).filter(tbl1.left
> >> tbl3.left).filter(tbl1.left < tbl3.right).subquery()
> > query = session.query(tbl1.id, tbl1.text,
> > tbl2.id.label('parent')).outerjoin((tbl2,
> > tbl2.left==sub_query.c.max_left))
>
> sub_query is a scalar subquery here, you don't want it in the FROM clause.
> do it like this:
>
> sub_query = session.query(func.max(tbl3.left).label('max_left')).\
> filter(tbl1.left < tbl3.left).\
> filter(tbl1.left < tbl3.right).subquery().as_scalar()
>
> query = session.query(tbl1.id, tbl1.text, tbl2.id.label('parent')).\
> outerjoin((tbl2,tbl2.left==sub_query))
>
>
>
> > rset = query.all()
>
> > I get the following error:
> > sqlalchemy.exc.OperationalError: (OperationalError) no such column:
> > anon_1.max_left
> > ...
>
> > I have printed the queries for the raw SQL, subquery and query objects
> > to assist in diagnosing what I am missing in the query/subquery.
>
> > ***************
>
> > 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)
> > ***************
> > SELECT max(nsm_1.lft) AS max_left
> > FROM nsm AS nsm_1, nsm AS nsm_2
> > WHERE nsm_2.lft > nsm_1.lft AND nsm_2.lft < nsm_1.rgt
> > ***************
> > 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 =
> > anon_1.max_left
> > ***************
>
> > Any help in resolving this would be greatly appreciated.
>
> > --
> > 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
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
>
>
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?
Kind Regards
--
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.
