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 at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
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.
