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.
Here is an abbreviated example of the setup:
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
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
?
Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.
associationproxy is used to create a read/write view of a target attribute across a relationship.
A proxy is a document authorizing a person to act on behalf of another person. When it comes to HOA voting, this means that a unit or homeowner may authorize someone else to represent them at an HOA meeting and to vote on their behalf.
Association Proxy¶ associationproxyis used to create a read/write view of a target attribute across a relationship. It essentially conceals the usage of a “middle” attribute between two endpoints, and can be used to cherry-pick fields from a collection of related objects or to reduce the verbosity of using the association object pattern.
If there is a relationship that links a particular Childto each Parent, suppose it’s called Child.parents, SQLAlchemy by default will load in the Child.parentscollection to locate all Parentobjects, and remove each row from the “secondary” table which establishes this link.
Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.
Instead of using the relationship.secondaryargument, you map a new class directly to the association table. The left side of the relationship references the association object via one-to-many, and the association class references the right side via many-to-one.
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.
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.
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
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.
With the given definition in place:
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']
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']
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With