Flask-Admin Hacks for Many-to-Many Relationships

Flask-Admin is a pretty powerful administration solution, which, however lacks several important features for managing models with many-to-many relationships (or at least clear documentation thereof). Here are some useful (albeit perhaps unreliable) hacks to get Flask-Admin SQLAlchemy models to dance the dance.

The following model definitions are used in these examples:

class Post( Base ):
    __tablename__ = 'posts'
    id = Column( Integer, primary_key=True )
    title = Column( Unicode( 255 ), nullable=False )
    tags = relationship( 'Tag', backref='posts', secondary='taxonomy' )

class Tag( Base ):
    __tablename__ = 'tags'
    id = Column( Integer, primary_key=True )
    name = Column( Unicode( 255 ), nullable=False )

taxonomy = Table(
    'taxonomy', Base.metadata,
    Column( 'post_id', Integer, ForeignKey( 'posts.id' ) ),
    Column( 'tag_id', Integer, ForeignKey( 'tags.id' ) ),
)

Many-to-many Search

The relationship between a Post and a Tag is a MANYTOMANY one using the taxonomy table to keep track of the relations.

Searchable columns in a ModelView are defined via the column_searchable_list list property. This list is expected to contain defined columns. However, adding the Tag.name column to the list yields a nasty InvalidRequestError: Could not find a FROM clause to join from. Tried joining to tags, but got: Can't find any foreign key relationships between 'posts' and 'tags'. Exception. Why is this?

Well it turns out that Flask-Admin adds table definitions as joins as seen here. Thus the column.table (Tags.__table__ does not actually contain any relationship information, the definition does.

One way to fix this is to override the init_search method of the view and modify the _search_joins after super has been called. Like so:

    class PostModelView( ModelView ):
        column_searchable_list = ( Post.title, Tag.name )

        def init_search( self ):
            r = super( PostModelView, self ).init_search()
            self._search_joins['tags'] = Tag.name
            return r

This way the join used is actually a valid one, containing relationship information.

This looks like it’s something not taken into account in the core init_search method and fixing this is non-trivial.

Many-to-Many Inline Models

Inline models in Flask-Admin are displayed inline with the main models if the the two are related in some way. However “some” does not include a many-to-many relationship, providing a further headache.

An exception is raised when trying, saying: Cannot find reverse relation for model Tags.

Using a custom InlineModelConverter we’re able to add a MANYTOMANY inline form view.

Most of the code is taken from the default converter.

class PostModelViewInlineModelConverter( InlineModelConverter ):
    def contribute( self, converter, model, form_class, inline_model ):
        mapper = object_mapper( model() )
        target_mapper = object_mapper( inline_model() )

        info = self.get_info( inline_model )

        # Find reverse property
        for prop in target_mapper.iterate_properties:
            if hasattr( prop, 'direction' ) and prop.direction.name == 'MANYTOMANY':
                if issubclass( model, prop.mapper.class_ ):
                    reverse_prop = prop
                    break
        else:
            raise Exception( 'Cannot find reverse relation for model %s' % info.model )

        # Find forward property
        for prop in mapper.iterate_properties:
            if hasattr( prop, 'direction' ) and prop.direction.name == 'MANYTOMANY':
                if prop.mapper.class_ == target_mapper.class_:
                    forward_prop = prop
                    break
        else:
            raise Exception( 'Cannot find forward relation for model %s' % info.model )
        child_form = info.get_form()
        if child_form is None:
            child_form = get_form(
                info.model, converter,
                only=PostModelView.form_columns,
                exclude=PostModelView.form_excluded_columns,
                field_args=PostModelView.form_args,
                hidden_pk=True
            )
        child_form = info.postprocess_form( child_form )

        setattr( form_class, forward_prop.key + '_add', self.inline_field_list_type(
            child_form, self.session, info.model, reverse_prop.key, info
        ) )

        return form_class

This class should probably be a generic one, like M2MModelViewInlineModelConverter. Having injected a tags_add key into the form we continue to maintain the nice tagging UI provided by Flask-Admin, while bringing in an inline interface which allows us to create new tags from the posts form.

One final piece of the puzzle is to actually map tags_add to the model upon save, delete, modify actions, otherwise the fields have no clue on how to process the data. Luckily this is much easier than coding the converter.

The PostModelView‘s on_model_change has to look like this:

    def on_model_change( self, form, model ):
        form.tags_add.populate_obj( model, 'tags' )
        self.session.add( model )

Simple. Patching this up in core seems to also be something that takes a lot of thought, especially to keep both the fancy tagging UI and the create UI.

Overall, Flask-Admin is dead easy to work with on mainstream things, but other functionality takes a bit of tinkering to accomplish. Let the hacking continue…