On Feb 8, 2014, at 2:52 PM, Iain Duncan <[email protected]> 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
