SQLAlchemy - order_by on relationship for join table

Question

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)?

score 35 · Answer 1 · edited Aug 26 '15 at 09:55

35

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

edited Aug 26 '15 at 09:55

Kijewski

25,517
12
101
143

answered Nov 14 '12 at 10:40

Nick Woodhams

11,977
10
50
52

3

Haha! Ran in to this problem and found your answer! Yours is the only one that works! – nathancahill Nov 02 '13 at 04:06

score 8 · Answer 2 · answered Oct 28 '11 at 18:24

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

score 0 · Answer 3 · answered Jul 21 '11 at 09:44

0

The problem is in permissionLinks relationship. Ordering its items by by Role.name makes no sense to me.

answered Jul 21 '11 at 09:44

Denis Otkidach

32,032
8
79
100

I want to display all the `RolePermission` rows associated with a given permission, and I want them displayed in order of `Role.name`. Currently I sort them after getting the results from the DB, but it seems I should be able to do that with SQLAlchemy. I display the `RolePermission` rows and not just `Permission` rows (via `permissions`) because `RolePermission` has info about creation date, creator, etc. that `Permission` does not have. – Sarah Vessels Jul 21 '11 at 15:48
But in your sample code you are trying to sort `RolePermission` objects associated with given _role_. – Denis Otkidach Jul 25 '11 at 08:02
Right. It seems like that should be doable, because each `RolePermission` has a `Role`, so why can't I sort `RolePermission`s by a property on `Role`? – Sarah Vessels Jul 29 '11 at 02:56

SQLAlchemy - order_by on relationship for join table

3 Answers3

Linked