How to display column with results from many-to-many query in Flask/SQLAlchemy

Question

I am trying to learn Python/Flask/SQLAlchemy by building a simple Wiki (heavily based off of a Flask-Admin example) but am struggling to understand how to get a new column from my many-to-many relationship to display.

I have successfully created the Wiki and have created a many-to-many relationship table for tags with no problem (and tagging works properly as far as I have seen), but I want to display the tags as a column and can't get the logic worked out.

GOAL: I want to display a column that shows the tags that are referenced by the many-to-many association table.

Here is a picture of what I am trying to accomplish:

Here is what I believe to be the relevant code:

wiki_tags_table = db.Table('wiki_tags', db.Model.metadata,
                           db.Column('wiki_id', db.Integer, db.ForeignKey('wiki.id')),
                           db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'))
                           )

class Wiki(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), unique=True)
    description = db.Column(db.Text)
    path = db.Column(db.Unicode(256))
    date_added = db.Column(db.DateTime)
    tags_id = db.Column(db.Integer, db.ForeignKey('tag.id'))
    tags = db.relationship('Tag', secondary=wiki_tags_table, backref=db.backref('wiki_tags_table', lazy='dynamic'))

    def __unicode__(self):
        return self.item

class WikiAdmin(sqla.ModelView):

    column_exclude_list = ['path']

    column_hide_backrefs = False

    form_overrides = {
        'path': form.FileUploadField
    }

    form_args = {
        'path': {
            'label': 'File',
            'base_path': file_path
        }
    }

    column_searchable_list = ('title', 'description', 'path')

    def __init__(self, session):
        super(WikiAdmin, self).__init__(Wiki, session)

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(64))

    def __unicode__(self):
        return self.name

I have been referencing these documents (mostly trying variations of backref) but haven't figured it out yet:

• http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-viii-followers-contacts-and-friends
• http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html

Answer 1

Not sure if this will help as I'm learning myself. But I had a similar issue where I wanted to display a column from a 'foreignkey table' and did it like this:

My modle.py

  from app import db

class Member(db.Model):
    __tablename__ = 'members'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(64), index=True)
    phone = db.Column(db.String(10), index=True)
    email = db.Column(db.String(120), index=True, unique=True)
    grade = db.relationship('Grade', backref='member')
    attendance = db.relationship('Attendance', backref='member')

    def __repr__(self):
        return '<User %r>' % self.name


class Grade(db.Model):
    __tablename__ = 'grades'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    member_id = db.Column(db.Integer, db.ForeignKey('members.id'))
    grade = db.Column(db.String(10))
    grade_date = db.Column(db.Date)

    def __repr__(self):
        return '<Grading %r>' % self.id

    def __str__(self):
        return self.grade

    def __unicode__(self):
        return self.grade


class Attendance(db.Model):
    __tablename__ = 'attendance'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    id_member = db.Column(db.Integer, db.ForeignKey('members.id'))
    attend_date = db.Column(db.Date)

    def __repr__(self):
        return '<Attenance %r>' % self.id

my views.py

from app.models import Member, Grade, Attendance
from app import app, admin, db
from flask_admin import BaseView, expose
from flask_admin.contrib.fileadmin import FileAdmin
from flask_admin.contrib.sqla import ModelView
import os.path as op


class AdminView(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    # column_list = ('id', 'name', 'parent')
    create_modal = True
    edit_modal = True


class MemberAdmin(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    can_view_details = True
    create_modal = True
    edit_modal = True
    form_columns = ['name', 'phone', 'email', 'grade', 'attendance']
    column_details_list = ['name', 'phone', 'email', 'grade', 'attendance']
    column_searchable_list = ['name', 'email']
    column_list = ('id', 'name', 'phone','email','grade')


class GradeAdmin(ModelView):
    column_display_pk = True  # optional, but I like to see the IDs in the list
    column_hide_backrefs = False
    column_list = ('id', 'member', 'grade', 'grade_date')
    form_choices = {'grade': [('Beginner', 'Beginner'), ('Yellow', 'Yellow'), ('Orange', 'Orange'),
                              ('Green 1', 'Green 1'), ('Green 2', 'Green 2'), ('Blue 1', 'Blue 1'),
                              ('Blue 2', 'Blue 2'), ('Purple 1', 'Purple 1'), ('Purple 2', 'Purple 2'),
                              ('Brown 1', 'Brown 1'), ('Brown 2', 'Brown 2'), ('Red 1', 'Red 1')]}


admin.add_view(MemberAdmin(Member, db.session, endpoint='member', category='Admin'))
admin.add_view(GradeAdmin(Grade, db.session, endpoint='grades', category='Admin'))
admin.add_view(ModelView(Attendance, db.session, endpoint='attendance', category='Admin'))

As I don't really know what I'm doing (yet) I think the bit that let me see the columns for my Member model with the extra column added which came from the Grade model was these lines in class MemberAdmin(ModelView):

column_hide_backrefs = False
can_view_details = True
...
form_columns = ['name', 'phone', 'email', 'grade', 'attendance']