[FIXED] SQLAlchemy lazy=dynamic with m2m relationship using association object pattern

Issue

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.

Solution

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.

Answered By - stefanobaldo