Michael thanks for the help, this is how I was able to get it working. Probably isn't the most efficient, but it works, I couldn't implement it the way you proposed because I still need to be able to do "a_row = b_row.A"
--------- sql_b = select([b_table, sql_a.c.name], b_table.c.a_id = sql_a.c.id).alias('b_query') mapper(b, sql_b, properties = { 'A' : relation(A, lazy=False, primaryjoin=(sql_a.c.id==sql_b.c.a_id))}) mapper(C, c_table, properties = { 'Bs' : relation(B, backref='C', cascade='all, delete-orphan', order_by=[sql_b.c.name, sql_c.c.value1, sql_c.c.value2]), }) Michael Bayer wrote: > On Dec 5, 2007, at 1:56 PM, David Gardner wrote: > > >> I have three tables a(a query of a really), b, c >> >> a has a 1-many relationship with b >> c has a 1-many relationship with b >> >> What I would like to do is in my mapper for table c, is sort the order >> of rows from b by a.name. >> I don't know how to do this or if it is possible. What I have looks >> like: >> >> sql_a = select([table_a], table_a.c.col1='some >> value').alias('a_query') >> >> mapper(B, b_table, properties = { >> 'A' : relation(sql_a, lazy=False, >> primaryjoin=(sql_a.c.id==table_b.c.a_id))}) >> >> mapper(C, c_table, properties = { >> 'Bs' : relation(B, backref='C', cascade='all, delete-orphan', >> order_by=[C.A.name, c_table.c.value1, >> c_table.c.value2]), >> }) >> >> This gets me an "AttributeError: 'InstrumentedAttribute' object has no >> attribute 'name'" >> > > this error is becuase C.A is not a gateway to the columns on the "A" > mapper. > > >> I have also tried: >> order_by=[A.name >> and >> order_by=[sql_a.c.name >> >> both get me this: ProgrammingError: (ProgrammingError) missing >> FROM-clause entry for table "sql_a.name" >> > > this error is because the relation from C->B has no relationship to > "A"s table at all, and it is not used in the generated query. to have > "A"s table be a part of it in all cases, youd have to map A's column > onto B's mapper, which is fairly messy: > > mapper(B, select([b_table, a.c.name], b_table.c.a_id==a.c.id)) > > you can try creating this mapper as a "non-primary" mapper and using > it for just the C->B relation: > > s = select([b_table, a.c.name], b_table.c.a_id==a.c.id) > mapper(C, ctable, properties={ > 'bs':relation(mapper(B, s, non_primary=True), order_by=[s.c.name]) > }) > > I havent tried the above myself so see if it works for you. > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---