SQLAlchemy中“一对多”场景下的排序取数

Python操作数据库表时常用SQLAlchemy做ORM，把关系数据库的表结构映射到对象上，并通过relationship做外键关联，方便进一步处理。

假设需要实现某个“一对多”的场景，从“一”这头需要取出“多”那头的全部对象，并按某个字段做排序，如何实现呢?

话不多说，show code。

本例使用sqlite3做数据库，创建Project和User两张表，两者是一对多关系。
核心是在User表中通过backref做反向引用时声明order_by即可。

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, backref, foreign, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Project(Base):
    __tablename__ = "project"

    id = Column(Integer, primary_key=True)
    name = Column(String(20))

    def __repr__(self):
        return "<Project(id='%d', name='%s')>" % (self.id, self.name)


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    project_id = Column(Integer)
    project = relationship(Project, primaryjoin=foreign(project_id) == Project.id,
                             backref=backref("users", order_by=id.desc()))

    def __repr__(self):
        return "<User(id='%d', name='%s', project_id='%d')>" % (self.id, self.name, self.project_id)


#  init database and create tables
def init_db():
    engine = create_engine('sqlite:///test.db')
    smaker = sessionmaker(bind=engine)
    session = smaker()

    Base.metadata.create_all(engine)
    return engine, smaker, session


#  prepare data
def init_data(session):
    project = Project(name='Project_A')
    session.add(project)
    session.commit()

    user_a = User(name='user_a', project_id=project.id)
    user_b = User(name='user_b', project_id=project.id)
    session.add_all([user_a, user_b])
    session.commit()


def main():
    engine, smaker, session = init_db()
    init_data(session)

    # test 
    projects = session.query(Project).all()
    for p in projects:
        print(p.users)


if __name__ == '__main__':
    main()

结果如图，可以看到User对象是按照id倒序排列。

1619770765752.jpg

参考：

• https://www.cnblogs.com/zhongyehai/p/11827011.html