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