SQLAlchemy order_by通过关联代理进行多对多关系

SQLAlchemy order_by通过关联代理进行多对多关系

为了进行排序

group.members

，必须在加载GroupMembership关联对象时使Persons可供排序。这可以通过联接来实现。

在当前配置中，访问

group.members

首先加载GroupMembership对象，填充

group.group_memberships

关系，然后在关联代理访问

GroupMembership.person

关系属性时为每个Person触发SELECt
。

相反，您想在同一查询中同时加载GroupMemberships和Persons，排序方式为

Person.last_name

：

class GroupMembership(Model):    __tablename__ = 'group_memberships'    id = Column(Integer, primary_key=True)    person_id = Column(Integer, ForeignKey('persons.id'), nullable=False)    group_id = Column(Integer, ForeignKey('groups.id'), nullable=False)    person = relationship('Person',    backref=backref('group_memberships',         cascade='all, delete-orphan'),    lazy='joined', innerjoin=True,    order_by='Person.last_name')    group = relationship('Group', backref=backref('group_memberships',      cascade='all, delete-orphan'))    # Other stuff

您需要定义

order_by='Person.last_name'

on标量关系属性

GroupMembership.person

而不是backref

Group.group_memberships

，这似乎很合逻辑。另一方面，

order_by

“指示加载这些项目时应应用的顺序”，因此在使用联合加载时有意义。由于将要连接多对一引用，并且外键不可为空，因此可以使用内部联接。

使用给定的定义：

In [5]: g = Group(name='The Group')In [6]: session.add_all([GroupMembership(person=Person(last_name=str(i)), group=g)   ...:       for i in range(30, 20, -1)])In [7]: session.commit()In [8]: g.members2017-06-29 09:17:37,652 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2017-06-29 09:17:37,653 INFO sqlalchemy.engine.base.Engine SELECT groups.id AS groups_id, groups.name AS groups_name FROM groups WHERe groups.id = ?2017-06-29 09:17:37,653 INFO sqlalchemy.engine.base.Engine (1,)2017-06-29 09:17:37,655 INFO sqlalchemy.engine.base.Engine SELECt group_memberships.id AS group_memberships_id, group_memberships.person_id AS group_memberships_person_id, group_memberships.group_id AS group_memberships_group_id, persons_1.id AS persons_1_id, persons_1.last_name AS persons_1_last_name FROM group_memberships JOIN persons AS persons_1 ON persons_1.id = group_memberships.person_id WHERe ? = group_memberships.group_id ORDER BY persons_1.last_name2017-06-29 09:17:37,655 INFO sqlalchemy.engine.base.Engine (1,)Out[8]: [<__main__.Person object at 0x7f8f014bdac8>, <__main__.Person object at 0x7f8f014bdba8>, <__main__.Person object at 0x7f8f014bdc88>, <__main__.Person object at 0x7f8f01ddc390>, <__main__.Person object at 0x7f8f01ddc048>, <__main__.Person object at 0x7f8f014bdd30>, <__main__.Person object at 0x7f8f014bde10>, <__main__.Person object at 0x7f8f014bdef0>, <__main__.Person object at 0x7f8f014bdfd0>, <__main__.Person object at 0x7f8f0143b0f0>]In [9]: [p.last_name for p in _]Out[9]: ['21', '22', '23', '24', '25', '26', '27', '28', '29', '30']

该解决方案的一个缺点是，

person

关系总是总是渴望加载，并在查询GroupMemberships时应用ORDER BY：

In [11]: session.query(GroupMembership).all()2017-06-29 12:33:28,578 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2017-06-29 12:33:28,578 INFO sqlalchemy.engine.base.Engine SELECt group_memberships.id AS group_memberships_id, group_memberships.person_id AS group_memberships_person_id, group_memberships.group_id AS group_memberships_group_id, persons_1.id AS persons_1_id, persons_1.last_name AS persons_1_last_name FROM group_memberships JOIN persons AS persons_1 ON persons_1.id = group_memberships.person_id ORDER BY persons_1.last_name2017-06-29 12:33:28,578 INFO sqlalchemy.engine.base.Engine ()Out[11]:     ...

…除非明确使用其他加载策略：

In [16]: session.query(GroupMembership).options(lazyload('person')).all()2018-04-05 21:10:52,404 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2018-04-05 21:10:52,405 INFO sqlalchemy.engine.base.Engine SELECt group_memberships.id AS group_memberships_id, group_memberships.person_id AS group_memberships_person_id, group_memberships.group_id AS group_memberships_group_id FROM group_memberships2018-04-05 21:10:52,405 INFO sqlalchemy.engine.base.Engine ()

如果您需要不时进行其他排序，则必须还原为发出使用明确的紧急加载和排序的完整查询，方法是：

In [42]: g = session.query(Group).    ...:     filter_by(id=1).    ...:     join(GroupMembership).    ...:     join(Person).    ...:     options(contains_eager('group_memberships')    ...:  .contains_eager('person')).    ...:     order_by(Person.last_name.desc()).    ...:     one()    ...:In [43]: [m.last_name for m in g.members]Out[43]: ['30', '29', '28', '27', '26', '25', '24', '23', '22', '21']