sqlalchemy + Pymysql - generated query on joins prefixes identifiers to columns

Question

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.

db_session.query(Role).join(Role.permissions).order_by(Permission.id.desc())

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

Role model

    __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'
    )

Permission model

    __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.

Answer 1

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.