Simple Many-to-Many issue in Flask-Admin

Question

I'm adding Flask-Admin to an existing Flask app (using Python 3, and MySQL with SQLAlchemy), and I simply cannot figure out how to get a many-to-many relationship to render correctly. I've read a number of questions about this here, and it looks like I am following the right practices.

I have a Quotation table, a Subject table, and a QuotationSubject table, which has to be an actual class rather than an association table, but I don't care about the extra columns in the association table for this purpose; they're things like last_modified that I don't need to display or edit. The relationships seem to work in the rest of the application.

Trimming out the fields and definitions that don't matter here, I have:

class Quotation(db.Model):
    __tablename__ = 'quotation'
    id = db.Column(db.Integer, primary_key=True)
    word = db.Column(db.String(50))
    description = db.Column(db.Text)
    created = db.Column(db.TIMESTAMP, default=db.func.now())
    last_modified = db.Column(db.DateTime, server_default=db.func.now())

    subject = db.relationship("QuotationSubject", back_populates="quotation")

    def __str__(self):
        return self.word

class Subject(db.Model):
    __tablename__ = 'subject'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    created = db.Column(db.TIMESTAMP, default=db.func.now())
    last_modified = db.Column(db.DateTime, server_default=db.func.now())

    quotation = db.relationship("QuotationSubject", back_populates="subject")

    def __str__(self):
        return self.name

class QuotationSubject(db.Model):
    __tablename__ = 'quotation_subject'
    id = db.Column(db.Integer, primary_key=True)
    quotation_id = db.Column(db.Integer, db.ForeignKey('quotation.id'), default=0, nullable=False)
    subject_id = db.Column(db.Integer, db.ForeignKey('subject.id'), default=0, nullable=False)
    created = db.Column(db.TIMESTAMP, default=db.func.now())
    last_modified = db.Column(db.DateTime, server_default=db.func.now())

    quotation = db.relationship('Quotation', back_populates='subject', lazy='joined')
    subject = db.relationship('Subject', back_populates='quotation', lazy='joined')

In my admin.py, I simply have:

class QuotationModelView(ModelView):
    column_searchable_list = ['word', 'description']

    form_excluded_columns = ['created', 'last_modified']
    column_list = ('word', 'subject')


admin.add_view(QuotationModelView(Quotation, db.session))

And that's it.

In my list view, instead of seeing subject values, I get the associated entry in the QuotationSubject table, e.g.

test    <QuotationSubject 1>, <QuotationSubject 17>, <QuotationSubject 18>
book    <QuotationSubject 2>

Similarly, in my create view, instead of getting a list of a dozen or so subjects, I get an enormous list of everything from the QuotationSubject table.

I've looked at some of the inline_models stuff, suggested by some posts here, which also hasn't worked, but in any case there are other posts (e.g. Flask-Admin view with many to many relationship model) which suggest that what I'm doing should work. I'd be grateful if someone could point out what I'm doing wrong.

Answer 1

First of all, I fear there's something missing from your question because I don't see the Citation class defined. But that doesn't seem to be the problem.

The most classic example of many-to-many relationships in Flask is roles to users. Here is what a working role to user M2M relationship can look like:

class RolesUsers(Base):
    __tablename__ = 'roles_users'

    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    role_id = db.Column(db.Integer(), db.ForeignKey('role.id'))

class Role(RoleMixin, Base):
    __tablename__ = 'role'

    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)

    def __repr__(self):
        return self.name

class User(UserMixin, Base):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), index=True, unique=True)

    roles = db.relationship('Role', secondary='roles_users',
                            backref=db.backref('users', lazy='dynamic'))

And in Flask-Admin:

from user.models import User, Role

admin.add_view(PVCUserView(User, db.session))
admin.add_view(PVCModelView(Role, db.session))

Note that the relationship is only declared once, with a backref so it's two-way. It looks like you're using back_populates for this, which I believe is equivalent.

For the case you're describing, it looks like your code declares relationships directly to the M2M table. Is this really what you want? You say that you don't need access to the extra columns in the QuotationSubject table for Flask-Admin. But do you need them elsewhere? It seems very odd to me to have a call to quotation or subject actually return an instance of QuotationSubject. I believe this is why Flask-Admin is listing all the QuotationSubject rows in the create view.

So my recommendation would be to try setting your relationships to point directly to the target model class while putting the M2M table as the secondary.

If you want to access the association model in other places (and if it really can't be an Association Proxy for some reason) then create a second relationship in each model class which explicitly points to it. You will then likely need to exclude that relationship in Flask-Admin using form_excluded_columns and column_exclude_list.