SQLAlchemy-连接表的order_by关系 [英] SQLAlchemy - order_by on relationship for join table
问题描述
我正在使用声明性SQLAlchemy,并且有三个模型:Role
,Permission
和RolePermission
.在我的Role
模型中,我具有以下内容:
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)
现在permissions
声明可以正常工作,并且与角色关联的权限按照我的期望(按名称)排序.但是,permissionLinks
失败,并出现以下错误:
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驱动程序] [SQL Server]多部分标识符"ROLES.NAME"未绑定.( 4104)(SQLExecDirectW); [42000] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]语句无法准备.(8180)')u'选择[ROLES_PERMISSIONS].[ROLE_ID]为[ROLES_PERMISSIONS_ROLE_ID] ,[ROLES_PERMISSIONS].[PERMISSION_ID]为[ROLES_PERMISSIONS_PERMISSION_ID],[ROLES_PERMISSIONS].[IS_DENIED] AS为[ROLES_PERMISSIONS_IS_DENIED] \ nFROM [ROLES_PERMISSIONS] \ nWHERE [ROLES_PERMISSIONS]? ORDER BY [ROLES].[NAME]'(19,)
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,)
问题在于Role
未加入,因此无法按Role.name
进行排序.我尝试指定primaryjoin=id == RolePermission.id1
,但这似乎并没有改变任何东西.如何在这种关系上指定联接,以便可以按联接表之一(即Role.name
)中的字段排序?
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
)?
推荐答案
我无法使所有这些解决方案正常工作,但是我找到了一种更简单的方法.
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")
在这里找到: http://docs.sqlalchemy.org/en/latest/orm /extensions/declarative/relationships.html
这篇关于SQLAlchemy-连接表的order_by关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!