On Feb 8, 2014, at 2:52 PM, Iain Duncan <iainduncanli...@gmail.com> wrote:
> Hi folks, I have a mapper that has an order by clause, and I was hoping to > get it to work with secondary sorts on the joined tables, wondering if this > is possible, or if not, how I could add some other kind of more complex > sorting? > > Here's what I hoped would work but didn't: > > > mappers['Membership'] = mapper(Membership, membership_table, properties = { > 'client': relation(Client, backref=backref('memberships', > cascade='all, delete, delete-orphan') ), > > 'billing_type' : relation(BillingType, backref='memberships'), > 'membership_type' : relation(MembershipType, backref='memberships'), > 'orgunit' : relation(Orgunit, backref=backref('memberships', > cascade='all, delete, delete-orphan') ), > 'roles_assocs' : relation(MembershipsRolesAssoc, cascade='all, > delete, delete-orphan', backref='membership') # was 'memberships' > }, order_by=[membership_table.c.date_end, client_table.c.name_last] ) > > > And the error: > OperationalError: (OperationalError) (1054, "Unknown column > 'client.name_last' in 'order clause'") 'SELECT membership.id AS > membership_id, membership.client_id AS membership_client_id, > membership.orgunit_id AS membership_orgunit_id, membership.date_start AS > membership_date_start, membership.date_end AS membership_date_end, > membership.billing_type_id AS membership_billing_type_id, > membership.membership_type_id AS membership_membership_type_id, > membership.is_active AS membership_is_active, membership.notes AS > membership_notes \nFROM membership \nWHERE %s = membership.orgunit_id ORDER > BY membership.date_end, client.name_last' (21L,) You’d need to configure a more complex relationship that includes the membership_table as well as the client_table in the “primaryjoin” condition. Some examples on how these more complex primaryjoins can be configured is at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins and http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper. These techniques can be used in older SQLAlchemy versions also though there are more configurational quirks involved. I can’t give more specifics with the info given since it’s not clear here how “client_table” relates to the MembershipRolesAssoc class, and actually it’s not clear either how “membership_table” is a target of this relation unless this is some kind of self-referential relationship.
signature.asc
Description: Message signed with OpenPGP using GPGMail