I'm using 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 a role come out 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]The multi-part identifier "ROLES.NAME" could not be bound. (4104) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 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
is not being joined, so it can't sort by Role.name
. I tried specifying primaryjoin=id == RolePermission.id1
, but that didn't seem to change anything. How can I specify a join on this relationship such that I can sort by a field in one of the joined tables (namely, Role.name
)?
The sqlalchemy backref is one of the type keywords and it passed as the separate argument parameters which has to be used in the ORM mapping objects. It mainly includes the event listener on the configuration attributes with both directions of the user datas through explicitly handling the database relationships.
I couldn't make any of these solutions work, however I found an easier 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
What you want is to order the role attribute of the RolePermission object. Passing order_by sets the order in the Role class.
Try this:
from sqlalchemy.orm import backref
permissionLinks = relationship(RolePermission, backref=backref("role", order_by=name))
setting an order for the back reference
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With