CODEWITHSUNDEEP
filtersqlalchemymany-to-many
Daniel Japs
Daniel Japs

Reputation: 13

SQL Alchemy Many To Many Filter List

I've got a many-to-many relationship set up as follows:

user_role = db.Table('user_role',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('role_id', db.Integer, db.ForeignKey('role.id'))
)

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(128), unique=True)
    password = db.Column(db.String(64))
    username = db.Column(db.String(128))
    roles = db.relationship('Role', secondary=user_role, backref="users")

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True)

Users can belong to multiple Roles and Roles can contain multiple Users.

I want to set up a query that returns only the Users who belong to a Role in my list. I already tried something like this:

role_list = ["admin", "premium"]
users = User.query.filter(User.roles.any(Role.name.in_(role_list)))

Upvotes: 0

Views: 489

Answers (1)

Daniel Japs
Daniel Japs

Reputation: 13

[UPDATE] After a lot of research i found a way to do it:

role_list = ["Admin", "Premium"]
users = User.query.join(Role, User.roles).filter(Role.name.in_(role_list))

For my purpose I have to "DISTINCT" the result to remove all identical tuples. It makes the pagination work!

role_list = ["Admin", "Premium"]
users = User.query.join(Role, User.roles).filter(Role.name.in_(role_list)).distinct().paginate(page=page, per_page=10)

Now all admins and premium members are listed.

Upvotes: 1

Related Questions