SQLAlchemy order_by 通过关联代理的多对多关系 [英] SQLAlchemy order_by many to many relationship through association proxy

问题描述

我使用关联对象在 SQLAlchemy 的 Flask 应用程序中设置了多对多关系.然后我在类之间设置了关联代理，以提供更直接的访问而不是通过关联对象.

I have a many to many relationship setup in a Flask app in SQLAlchemy using a Association Object. I then have have assocation proxies setup between the the classes, to give more direct access rather than going via the association object.

这是设置的缩写示例:

class Person(Model):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True)
    last_name = Column(Text, nullable=False)
    groups = association_proxy('group_memberships', 'group')
    # Other stuff

class Group(Model):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)
    name = Column(Text, nullable=False)
    members = association_proxy('group_memberships', 'person')
    # Other stuff

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', uselist=False, backref=backref('group_memberships', cascade='all, delete-orphan'))
    group = relationship('Group', uselist=False, backref=backref('group_memberships', cascade='all, delete-orphan'))    
    # Other stuff

我终生无法弄清楚的是如何让 group.members 返回的成员按他们的 last_name 排序?

What I cannot for the life of me figure out is how to get the members returned by group.members to be sorted by their last_name?

推荐答案

为了对 group.members 进行排序，您必须在加载 GroupMembership 关联对象时让 Persons 可用于排序.这可以通过连接来实现.

In order to sort group.members you have to have the Persons available for sorting while loading the GroupMembership association objects. This can be achieved with a join.

在您当前的配置中，访问 group.members 首先加载 GroupMembership 对象，填充 group.group_memberships 关系，然后在关联代理访问时为每个 Person 触发一个 SELECTGroupMembership.person 关系属性.

In your current configuration accessing group.members first loads the GroupMembership objects, filling group.group_memberships relationship, and then fires a SELECT for each Person as the association proxy accesses the GroupMembership.person relationship attributes.

相反，您希望在同一查询中同时加载 GroupMemberships 和 Persons，按 Person.last_name 排序:

Instead you want to load both the GroupMemberships and Persons in the same query, sorted by 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

您需要在标量关系属性 GroupMembership.person 上定义 order_by='Person.last_name' 而不是 backref Group.group_memberships>，这似乎是合乎逻辑的事情.另一方面 order_by指示加载这些项目时应应用的顺序"，因此在使用 加入加载.由于您将加入多对一引用并且外键不可为空，因此您可以使用内部联接.

You need to define the order_by='Person.last_name' on the scalar relationship attribute GroupMembership.person instead of the backref Group.group_memberships, which could seem like the logical thing to do. On the other hand order_by "indicates the ordering that should be applied when loading these items", so it makes sense when using joined loading. Since you'll be joining a many-to-one reference and the foreign key is not nullable, you can use an inner join.

使用给定的定义:

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.members
2017-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_name
2017-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:

A downside of this solution is that the person relationship is always loaded eagerly and the ORDER BY applied when querying for GroupMemberships:

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_name
2017-06-29 12:33:28,578 INFO sqlalchemy.engine.base.Engine ()
Out[11]: 
    ...

...除非明确使用其他加载策略:

...unless another loading strategy is used explicitly:

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_memberships
2018-04-05 21:10:52,405 INFO sqlalchemy.engine.base.Engine ()

如果您不时需要替代排序，您将不得不恢复发布使用 显式预加载 并按以下方式排序:

If you need alternate orderings from time to time, you'll have to revert to issuing a full query that uses explicit eager loading and order by:

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']

这篇关于SQLAlchemy order_by 通过关联代理的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！