Add relationship backref order_by with alembic

Question

I recently added Alembic to my Flask project and now my SQLAlchemy relationship sorting with order_by is no longer working. I believe this is because the migration file that is run is not picking up the order_by argument and I'm not sure how to get it in.

My model looks something like...

class Project(db.Model):
    __tablename__ = 'project'

    id = db.Column(db.Integer(), primary_key=True)


class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer(), primary_key=True)
    project_id = db.Column(db.ForeignKey(Project.id, ondelete="cascade"), index=True, nullable=False)
    project = db.relationship(Project, backref='members', order_by="User.id.desc()")

In the Alembic migration, I see the following...

    ...

op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('project_id', sa.Integer(), nullable=False),
    ...
    sa.ForeignKeyConstraint(['project_id'], ['project.id'], ondelete='cascade'),
)
op.create_index(op.f('ix_user_project_id'), 'user', ['project_id'], unique=False)

...

Looking at the migration, I'm not quite sure how SQLAlchemy is actually handling the relationship since I don't see any sort of backref mentioned anywhere, I guess SQLAlchemy just runs a query to get a project's users.

Any help would be greatly appreciated!

score 0 · Accepted Answer · answered Jul 15 '20 at 08:23

The migration file is correct, at DB level there is no backref, there is just the created foreign key linking the users entities with the projects. In fact, as you said, sqlalchemy will issue a new query the first time you access a user's user.project attribute, joining on the project_id field. Just as a side note, if you want to join the projects' columns altogether while querying users, you should use the joinedload option

Regarding the "order_by" used in the relationship declaration, it is only applied to the loaded relationship, such as in this example from the official docs:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", order_by="Child.id")

In the example above, when accessing parent.children, the results will be ordered by the children's IDs.

In your case this does not make sense because it's a many to one relationship, and for each user you only have one project. Maybe you should move it to the project model:

class Project(db.Model):
    __tablename__ = 'project'

    id = db.Column(db.Integer(), primary_key=True)
    members = db.relationship('User', backref='project', order_by='User.id.desc()')

This way, whenever you access a project's project.members attribute you will get a sorted list of users.

Add relationship backref order_by with alembic

1 Answers1