How can SQLAlchemy association_proxy be used bi-directionally?

Question

I'm working with Flask-SQLAlchemy to create a many-to-many relationship using Association Objects and association_proxy, but I'm running into an issue: When I use association_proxy, I can set it up such that when I append a new instance to one side of the relationship, the Association Object will be created, but if I attempt to add an instance from the other side of the relationship, the Association Object constructor won't be passed the correct instance.

Here's a simplified example where Club has many People, and People have many Club, and the Association Object is a Membership:

class Club(db.Model):
    __tablename__ = 'clubs'

    id = db.Column(db.Integer, db.Sequence('clubs_id_seq'), autoincrement=True, primary_key=True)
    name = db.Column(db.String(255))
    memberships = db.relationship('Membership', backref='club')
    members = association_proxy('memberships', 'club') # Can I pass a Person instance to Membership here? 
    created_at = db.Column('created_at', db.DateTime, default=datetime.datetime.now())

    def __init__(self, name):
        self.name = name

class Person(db.Model):
    __tablename__ = 'people'

    id = db.Column(db.Integer, db.Sequence('people_id_seq'), autoincrement=True, primary_key=True)
    name = db.Column(db.String(255))
    memberships = db.relationship('Membership', backref='person')
    clubs = association_proxy('memberships', 'club')
    created_at = db.Column('created_at', db.DateTime, default=datetime.datetime.now())

    def __init__(self, name):
        self.name = name

class Membership(db.Model):
    __tablename__ =  'memberships'

    person_id = db.Column('person_id', db.Integer, db.ForeignKey('people.id'), primary_key=True)
    club_id = db.Column('club_id', db.Integer, db.ForeignKey('clubs.id'), primary_key=True)
    club = db.relationship('Club', backref='membership')
    joined_at = db.Column('joined_at', db.DateTime, default=datetime.datetime.now())

    def __init__(self, club):
        self.club = club

I'm using association_proxy because more than just the connection between Club and Person is important; I also want to be able to retrieve the joined_at property of the Membership if needed. In most cases, though, I only will query and append to .members of a Club or .clubs of a Person. In this example, I can create a new Membership by calling person_instance.clubs.append(club_instance), but since the constructor of Membership can only take a single argument, how can I call club_instance.members.append(person_instance) if I were to work backwards? It seems lame that I could potentially iterate over club_instance.members, but have to remember not to append(). Am I missing something?

score 2 · Accepted Answer · edited May 23 '17 at 12:21

Found my answer in this Google Groups discussion. Here's the relevant portion:

class DossierTarife(Base):
    __tablename__ = 'tarifer_dossier'
    IdDossier = Column(Integer, ForeignKey('dossier.IdDossier'), primary_key=True)
    IdAt = Column(Integer, ForeignKey('article_tarife.IdAt'), primary_key=True)

    dossier = relationship(Dossier, backref=backref("tarifer_dossier", cascade="all, delete-orphan"))
    article_tarife = relationship(ArticleTarife, backref=backref("tarifer_dossier"))

class Dossier(Base):
    __tablename__ = 'dossier'
    IdDossier = Column('IdDossier', Integer, primary_key=True)
   ...

class ArticleTarife(Base):
    __tablename__ = 'article_tarife'
    IdAt = Column('IdAt', Integer, primary_key=True)
   ...

Dossier.LesTar = association_proxy("tarifer_dossier", "article_tarife", creator=lambda art:DossierTarife(article_tarife=art))
ArticleTarife.LesTar = association_proxy("tarifer_dossier", "dossier", creator=lambda dos:DossierTarife(dossier=dos))

I assigned my association_proxy directly within the class definition rather than doing it after the fact as above, and that worked well, too.

Also very helpful was this SO answer describing how to properly delete Association Object rows:

user = relationship(User, backref=backref('user_to_groups', cascade='all, delete-orphan'))
group = relationship(Group, backref=backref('group_to_user', cascade='all, delete-orphan'))

How can SQLAlchemy association_proxy be used bi-directionally?

1 Answers1

Linked