how to add db.Table of flask-sqlalchemy to flask-admin?

Question

When integrate the flask-security with flask-admin, the flask-security need create a table named roles_users with db.Table of flask-sqlalchemy:

    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')))

Those class based on db.Model of flask-sqlalchemy can register to flask-admin as follow:

    admin.add_view(ModelView(User, db.session))

I have check the source of flask-sqlalchemy, and can't figure out the relation between db.Table and db.Model

What i want to know is how to register roles_users to flask-admin.

Answer 1

Normally you don't need to add the user-roles junction table. Add the Users and Roles as separate views but in the Users view include the roles relationship. For example:

Given the following two models and table:

class Role(db.Model, RoleMixin):
    __tablename__ = 'roles'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(length=64), unique=True)
    description = db.Column(db.Unicode(length=255), nullable=True)

    def __unicode__(self):
        return u"{name} ({role})".format(name=self.name, role=self.description or 'Role')

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


class User(db.Model, UserMixin):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)

    first_name = db.Column(db.Unicode(length=255), nullable=False)
    last_name = db.Column(db.Unicode(length=255), nullable=False)

    email = db.Column(db.Unicode(length=254), unique=True, nullable=True)
    password = db.Column(db.Unicode(length=255), nullable=False)
    active = db.Column(db.Boolean(), default=False)

    roles = db.relationship('Role', secondary=user_to_role, backref=db.backref('users', lazy='select'))

    def __unicode__(self):
        return u"{first_name} ({last_name})".format(first_name=self.first_name, last_name=self.last_name)

Use these two views (Flask-Admin 1.4):

class UserView(ModelView):
    can_view_details = True
    column_list = ['first_name', 'last_name', 'email', 'roles', 'active']
    column_default_sort = ('last_name', False)
    column_filters = [
        'first_name',
        'last_name',
        'email',
        'active',
        'roles.name',
    ]
    column_details_list = [
        'first_name', 'last_name', 'email', 'active', 'roles',
        'confirmed_at', 'last_login_at', 'current_login_at', 'last_login_ip', 'current_login_ip', 'login_count',
    ]
    form_columns = [
        'first_name', 'last_name', 'email', 'active', 'roles',
    ]


class RoleView(ModelView):
    column_list = ['name', 'description']

    form_columns = ['name', 'description']