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

Try something like what I've done, with a special column formatter for the many-to-many fields. This works for me.

def many_to_many_formatter(view, context, model, name: str) -> str:
    """This function formats the many-to-many fields for display in the list view"""
    try:
        rel_field, col_name = name.split(".")
        list_of_tags: list = getattr(model, rel_field)
        list_of_results: list = [str(getattr(tag, col_name)) for tag in list_of_tags]
        new_str: str = ", ".join(list_of_results)
    except Exception:
        current_app.logger.exception("Error trying to format many-to-many relationship field in list view!")
        new_str = ""

    return new_str


class SpecialView(ModelView):
    """Flask-Admin view"""

    column_list = (
        "model",
        "description",
        # many-to-many field
        "parts_rel.part_num"
    )

    column_formatters = {
        "parts_rel.part_num": many_to_many_formatter,
    }

Answer 2

I needed to achieve identical thing just now, what worked for me is just adding column_list field in ViewModel class, so in this case, that would be

class WikiAdmin(sqla.ModelView):

    ...
    column_list = ('title', 'description', 'dataadded', 'tags')

This is old question, but this is first thread (and, arguably, the only relevant) I stumbled upon while trying to figure out for myself how to do it, so maybe this answer will save someone's time

Answer 3

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

Answer 4

The relationship attribute loads the related table as a list of objects, so you should be able to print all wiki items and all associated tags like this in a view:

for wiki_item in Wiki.query.all():
    print wiki_item.title
    for tag in wiki_item.tags:
        print tag.name 

What happens if you try that?