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