CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Gabriel Siqueira
Gabriel Siqueira

Reputation: 23

Flask-SQLAlchemy Query records by relationship attribute

In the app I've been working on uses Flask-User and their suggested models for authorization control. Here's an example:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)

    # User Authentication information
    username = db.Column(db.String(50), nullable=False, unique=True)
    password = db.Column(db.String(255), nullable=False, default='')

    # User Email information
    email = db.Column(db.String(255), nullable=False, unique=True)
    confirmed_at = db.Column(db.DateTime())

    # User information
    is_enabled = db.Column(db.Boolean(), nullable=False, default=False)


    # Relationships
    roles = db.relationship('Role', secondary='user_roles',
                            backref=db.backref('users', lazy='dynamic'))

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

class UserRoles(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id', ondelete='CASCADE'))
    role_id = db.Column(db.Integer(), db.ForeignKey('role.id', ondelete='CASCADE'))

In addition, as part of the Flask-User/Flask-Login, current_user gives me a User object corresponding to the current user of the app (if logged in) and, as defined in our model, current_user.roles returns a list of Role objects, corresponding to the roles that are bound to the current user by the UserRoles table.

Now, at some point, I want to be able to query all users that have any of the roles the current user has. How can that be achieved given our current_user.roles list and my models in SQL Alchemy? How can I select all user where any of its User.roles match any of our current_user.roles?

Upvotes: 1

Views: 4764

Answers (1)

stamaimer
stamaimer

Reputation: 6475

Add __repr__ to Role as follows:

class Role(db.Model):

    #...

    def __repr__(self):

        return self.id

And you can use following query to achieve your requirement.

User.query.filter(User.roles.any(Role.id.in_([role.id for role in current_user.roles]))).all()

Upvotes: 6

Related Questions