SQLAlchemy - order_by by relationship for join table

Question

I use declarative SQLAlchemy and I have three models: Role , Permission and RolePermission . In my Role model, I have the following:

 class Role(Base): name = Column(u'NAME', VARCHAR(50), nullable=False, unique=True) permissionLinks = relationship(RolePermission, backref="role", order_by=name) permissions = relationship(Permission, backref=backref("roles", order_by=name), secondary=RolePermission.__table__, order_by=Permission.name) 

Now the permissions declaration works fine, and the permissions associated with the role are sorted as I expect (by name). However, permissionLinks fails with the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Multiple Identifier "ROLES.NAME" cannot be connected. (4104) (SQLExecDirectW); [42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Statements cannot be prepared. (8180) ') u'SELECT [ROLES_PERMISSIONS]. [ROLE_ID] AS [ROLES_PERMISSIONS_ROLE_ID], [ROLES_PERMISSIONS]. [PERMISSION_ID] AS [ROLES_PERMISSIONS_PERMISSION_ID], [ROLES_PERMISSIONS]. [IS_DENIED] AS [ROLES_PERMISSIONS_IS_DENIED] \ nFROM [ROLES_PERMISSIONS] \ nWHERE [ROLES_PERMISSIONS]. [ROLE_ID] =? ORDER BY [ROLES]. [NAME] '(19,)

The problem is that Role does not connect, so it cannot sort by Role.name . I tried to specify primaryjoin=id == RolePermission.id1 , but that did not change anything. How can I specify a connection in this regard so that I can sort by field in one of the joined tables (namely Role.name )?

Answer 1

I could not get any of these solutions to work, however I found a simpler way.

 from sqlalchemy.ext.declarative import declarative_base class User(Base): # .... addresses = relationship("Address", order_by="desc(Address.email)", primaryjoin="Address.user_id==User.id") 

Found here: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/relationships.html

Answer 2

You want to order the role attribute of the RolePermission object. Passing order_by sets the order in the Role class.

Try the following:

 from sqlalchemy.orm import backref permissionLinks = relationship(RolePermission, backref=backref("role", order_by=name)) 

setting backlink order

Answer 3

The problem is the permissionLinks ratio. Ordering its elements on Role.name makes no sense to me.