flask-sqlalchemy: how to make a selectable button to select the users according to his role id?

Question

This is based on the flask-admin example.

I want to build the selectable drop button to select the users in the database according to the role id instead of all users.

For example: Say superusers(role id=2), how can I make a selectable button that restrict only the users are superusers can be selected (in my class Project)?

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


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

     def __str__(self):
        return self.name

class User(db.Model, UserMixin):
     id = db.Column(db.Integer, primary_key=True)
     roles = db.relationship('Role', secondary=roles_users,
                         backref=db.backref('users', lazy='dynamic'))
     email = db.Column(db.String(255), unique=True)

     def __str__(self):
         return self.email

class Project(db.Model):
      *some code here make the selectable button to select users that are superusers*

The role table:

The role-user table:

Answer 1

Your User model defines a many-to-many relationship with the Role model. You can use this relationship to query users by role id.

users = User.query.join(User.roles).filter(Role.id == 123).all()

Alternatively, you can query the Role first and then use the users attribute to access the users for the role.

role = Role.query.get(123)
users = role.users

For more information, see the SQLAlchemy documentation:

• Many-to-many relationships
• Query.join()