When trying to order by or filter by through a many to many relationship the generated query prefixes identifiers to the secondary tables while the column name itself stays the same. below is the generated query.
SELECT roles.id AS roles_id, roles.name AS roles_name, roles.description AS roles_description, roles.created_at AS roles_created_at, roles.updated_at AS roles_updated_at, permissions_1.id AS permissions_1_id, permissions_1.name AS permissions_1_name, permissions_1.slug AS permissions_1_slug, permissions_1.created_at AS permissions_1_created_at, permissions_1.updated_at AS permissions_1_updated_at FROM roles LEFT OUTER JOIN (role_permissions AS role_permissions_1 INNER JOIN permissions AS permissions_1 ON permissions_1.id = role_permissions_1.permission_id) ON roles.id = role_permissions_1.role_id // This is the part that causes the issue ORDER BY permissions.slug DESC
The call through sqlalchemy.
The association table.
role_permissions_table = Table( 'role_permissions', db.Model.metadata, Column( 'role_id', Integer, ForeignKey('roles.id'), nullable=False ), Column( 'permission_id', Integer, ForeignKey('permissions.id'), nullable=False ), Column( 'created_at', DateTime, default=datetime.utcnow ), Column( 'updated_at', DateTime, onupdate=datetime.utcnow ) )
__tablename__ = 'roles' id = Column(Integer, primary_key=True) name = Column(String(120)) description = Column(String(120)) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, onupdate=datetime.utcnow) permissions = relationship( 'Permission', secondary='role_permissions', lazy='joined' )
__tablename__ = 'permissions' id = Column(Integer, primary_key=True) name = Column(String(120)) slug = Column(String(150)) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, onupdate=datetime.utcnow)
Id like to know how i can either prevent the identifiers from being prefixed or if their is a proper way to make this work, been reading through the sqlalchemy docs, havent found anything on many to many relationship filtering/ordering. Thanks in advance.
Nevermind, fixed it, I missed an assignment operation on the query object the above one should work as intended since i was actually building the query based on external parameters. Im kicking myself right now, apologies to anyone that wasted their time trying to figure this out lol.