CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
stefanobaldo
stefanobaldo

Reputation: 2063

SQLAlchemy lazy=dynamic with m2m relationship using association object pattern

I have a simple m2m relationship between users and roles tables:

users_roles = db.Table('users_roles',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('role_id', db.Integer, db.ForeignKey('roles.id')),
    db.Column('is_primary', db.Boolean)
)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column('id', db.Integer, primary_key=True)
    roles = db.relationship('Role', secondary=users_roles, lazy='dynamic', backref=db.backref('users', lazy='dynamic'))

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column('id', db.Integer, primary_key=True)
    users = db.relationship('User', secondary=users_roles, lazy='dynamic', backref=db.backref('roles', lazy='dynamic'))

To add a record to the users_roles table, I have to do something like this:

role = Role.get(1)
user = User()
user.roles.append(role)
db.session.add(user)
db.session.commit()

That is okay, but I have a column named is_primary in the users_roles table that should also be populated.

I changed my code to use the Association Object Pattern as described in the SQLAlchemy documentation.

Now my code looks like this:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column('id', db.Integer, primary_key=True)

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column('id', db.Integer, primary_key=True)

class UserRole(db.Model):
    __tablename__ = 'users_roles'
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), primary_key=True)
    is_primary = db.Column(db.Boolean)

    user = db.relationship(User, backref="users_roles")
    role = db.relationship(Role, backref="users_roles")

User.roles = association_proxy("users_roles", "role")
Role.users = association_proxy("users_roles", "user")

It works nice, but I still have a problem. Is it possible that User.roles (added with the association proxy) returns an AppenderBaseQuery that I can add more filters, e.g. User.query.get(1).roles.filter_by(...)? I was used to do that with the plain many-to-many relationship using lazy=dynamic in the relationship declaration, but after giving a class mapping to the association table it seems that I cannot do it anymore. Is there a way to achieve that?

@IfLoop I followed your recommendation in this post. Your help would be much appreciated.

Upvotes: 5

Views: 2628

Answers (2)

Lucas
Lucas

Reputation: 1

Way too late for helping you but I asked myself the same question, and this paragraph of the docs shed some light on what is possible to do from proxy associations: https://docs.sqlalchemy.org/en/14/orm/extensions/associationproxy.html#querying-with-association-proxies

In summary from what I understood: this is not explicitly possible but For association proxies where the immediate target is a related object or collection, relationship-oriented operators can be used instead, such as .has() and .any()

I'm not sure this will help me, but I'm laying this there if it can ever point someone to their solution

Upvotes: 0

stefanobaldo
stefanobaldo

Reputation: 2063

Well, I ended up filtering roles using the following code:

roles = Role.query.filter_by(...).join(UserRole).join(User).filter_by(id=1)

I still want to be able to do something like this:

roles = User.query.get(1).roles.filter_by(...).all()

Anyway if I get no answers in a few days I will accept this as an answer.

Upvotes: 3

Related Questions