This works, thanks, but how do I control the ordering?
I want to order by the parent, then the child.
If I do:
grandchildren = session.query(Node).filter(Node.c.type==var1).\
order_by(Node.name).\
join('Parent', aliased=True).filter(Node.c.type==var2).\
order_by(Node.name).\
join('Parent',aliased=True,from_joinpoint=True).\
filter(Node.uid==var3).all ()
it sorts by child then parent. I can leave the first order_by out,
but that still leaves the children unsorted. I tried writing the joins
backwards in an attempt to control the ordering:
grandchildren = session.query(Node).filter(Node.uid==var3).\
join('Children', aliased=True).filter(Node.c.type==var2).\
order_by(Node.name).\
join('Children',aliased=True,
from_joinpoint=True).filter(Node.c.type==var1).\
order_by(Node.name).all()
but this doesn't work (it runs but I only get one row).
Michael Bayer wrote:
> On Feb 7, 2008, at 12:28 PM, David Gardner wrote:
>
>
>> I have a self referential hierarchy structure in a table that I have
>> mapped with SA 0.4. What I would like to do is query the table given a
>> node, and find its grandchildren based on some criteria in both the
>> child and grandchildren nodes. In SQL I would do:
>>
>> SELECT * FROM nodehierarchy AS grandparent
>> JOIN nodehierarchy AS parent ON parent.parentuid = grandparent.uid
>> JOIN nodehierarchy AS child ON child.parentuid = parent.uid
>> WHERE grandparent.uid = <<some_value>>
>> AND parent.type=<<different_value>>
>> AND child.type=<<another_value>>
>> ORDER BY parent.name, child.name;
>>
>>
>> Following the third example from
>> http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential_query
>> I have the following:
>>
>> grandchildren = session.query(Node).filter(Node.c.type==var1).\
>> join('Parent', aliased=True).filter(Node.c.type==var2).\
>> join('Parent',aliased=True,
>> from_joinpoint=True).filter(Node.uid==var3).all () #
>> order_by(Node.Parent.name, Node.name).all()
>>
>> grandchildren.sort (key=lambda i: (i.Parent.name,i.name))
>>
>> However the commented out order_by() doesn't work I get
>> "AttributeError:
>> 'InstrumentedAttribute' object has no attribute 'name'"
>> As you can see I am currently sorting client side.
>>
>
> place the order_by() call for each joined alias right after that join
> is created, i.e.:
>
> grandchildren = session.query(Node).filter(Node.c.type==var1).\
> join('Parent', aliased=True).filter(Node.c.type==var2).\
> order_by(Node.name).join('Parent',aliased=True,
> from_joinpoint=True).order_by(Node.name).filter(Node.uid==var3).all()
>
> the Node.name column is aliased against the alias generated by the
> most recent join() call.
>
>
>
>
>
> >
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
