How to handle ordered many-to-many relationship (a

Question

I have a many-to-many relationship between declarative models Page and Survey, which is mediated by association proxies because the order in which pages appear in a survey is important, so the cross-linking table has an additional field.

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.ext.associationproxy import association_proxy
db = SQLAlchemy()

class Page (db.Model):
    id = db.Column(db.Integer, primary_key = True)
    surveys = association_proxy('page_surveys', 'survey')

class Survey (db.Model):
    id = db.Column(db.Integer, primary_key = True)
    pages = association_proxy('survey_pages', 'page')

class SurveyPage (db.Model):
    survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'), primary_key = True)
    page_id = db.Column(db.Integer, db.ForeignKey('page.id'), primary_key = True)
    ordering = db.Column(db.Integer)  # 1 means "first page"
    survey = db.relationship('Survey', backref = 'survey_pages')
    page = db.relationship('Page', backref = 'page_surveys')

Now I want to offer a form through Flask-Admin that lets the user add pages to a survey. Ideally, the order in which the user fills the pages into the form determines the value of SurveyPage.ordering. This doesn't do the trick (the form cannot be rendered, see last bit of traceback at bottom of post):

from flask.ext.admin.contrib.sqla import ModelView
from flask.ext.admin import Admin

admin = Admin(name='Project')

class SurveyView (ModelView):
    form_columns = ('pages',)
    def __init__ (self, session, **kwargs):
        super(SurveyView, self).__init__(Survey, session, name='Surveys', **kwargs)

admin.add_view(SurveyView(db.session))

This works, but it doesn't do what I want (it lets me associate SurveyPage objects with the survey, but I would have to edit the ordering fields in a separate form):

class SurveyView (ModelView):
    form_columns = ('survey_pages',)
    # ...

I understand that I'll probably have to do some hacking by overriding sqla.ModelView.form_rules as well as inserting some HTML and Javascript into templates that inherit from admin/model/create.html et al. Unfortunately, I have very little experience with Flask-Admin, so figuring out how to tackle that on my own will take too much time. To make things worse, the documentation and example code don't seem to cover much beyond the basics. Help will be much appreciated!

---

Last bit of traceback from failing form:

File ".../python2.7/site-packages/flask_admin/contrib/sqla/form.py", line 416, in find
raise ValueError('Invalid model property name %s.%s' % (model, name))

ValueError: Invalid model property name <class 'project.models.Survey'>.pages

Answer 1

Final answer ready

The first part below is the original answer, the additions that complete the answer are appended at the end.

Original answer: storing the input

By now I have a partial solution to my own question. The form field works in the way I want and the input is correctly saved to the database. There is just one aspect missing: when I open the edit form of a pre-existing Survey, the Pages that were previously added to the Survey do not show up in the form field (in other word, the field is not pre-populated).

I will edit this post if I find the final solution myself. The bounty will be awarded to anyone who fills the final gap first. Please submit a new answer if you have the golden hint!

To my own surprise, I have not needed to do anything with templates yet. The trick lies mostly in avoiding both Survey.pages and Survey.survey_pages as form columns, instead using a different name as an "extra" field with a custom form field type. Here is the new version of the SurveyView class:

class SurveyView (ModelView):
    form_columns = ('page_list',)
    form_extra_fields = {
        # 'page_list' name chosen to avoid name conflict with actual properties of Survey
        'page_list': Select2MultipleField(
            'Pages',
             # choices has to be an iterable of (value, label) pairs
             choices = db.session.query(Page.id, Page.name).all(),
             coerce = int ),
    }

    # handle the data submitted in the form field manually
    def on_model_change (self, form, model, is_created = False):
        if not is_created:
            self.session.query(SurveyPage).filter_by(survey=model).delete()
        for index, id in enumerate(form.page_list.data):
            SurveyPage(survey = model, page_id = id, ordering = index)

    def __init__ (self, session, **kwargs):
        super(SurveyView, self).__init__(Survey, session, name='Surveys', **kwargs)

Select2MultipleField is a variant of flask.ext.admin.form.fields.Select2Field which I adapted by simply copy-pasting and modifying the code. I gratefully use flask.ext.admin.form.widgets.Select2Widget which already allows multiple selection if you pass the right constructor argument. I have included the source code at the bottom of this post in order to not break up the flow of the text (edit: the source code at the bottom of this post is now updated to reflect the final answer, which does not use the Select2Widget anymore).

The body of the SurveyView class contains a database query, which means that it needs the application context with an actual database connection. In my case that is a problem because my Flask application is implemented as a package with multiple modules and sub-packages, and I avoid cyclical dependencies. I have solved it by importing the module that contains the SurveyView class within my create_admin function:

from ..models import db

def create_admin (app):
    admin = Admin(name='Project', app=app)
    with app.app_context():
        from .views import SurveyView
    admin.add_view(SurveyView(db.session))
    return admin

In order to pre-populate the field in the edit form, I suspect I'll need to set SurveyView.form_widget_args with a 'page_list' field. So far it's still completely obscure to me what needs to be in that field. Any help is still very much appreciated!

---

Addition: pre-populating the select2 field

Automatic pre-filling of form fields that Flask-Admin knows how to handle is done in flask.ext.admin.model.base.BaseModelView.edit_view. Unfortunately, out of the box it doesn't provide any hooks à la on_model_change to add custom pre-filling actions. As a workaround, I made a subclass that overrides edit_view to include such a hook. The insertion is just a single line, here shown in context:

    @expose('/edit/', methods=('GET', 'POST'))
    def edit_view(self):
        # ...

        if validate_form_on_submit(form):
            if self.update_model(form, model):
                if '_continue_editing' in request.form:
                    flash(gettext('Model was successfully saved.'))
                    return redirect(request.url)
                else:
                    return redirect(return_url)

        self.on_form_prefill(form, id)  # <-- this is the insertion

        form_opts = FormOpts(widget_args=self.form_widget_args,
                             form_rules=self._form_edit_rules)

        # ...

In order to not cause problems for model views that don't use the hook, the derived class obviously also has to provide a no-op as the default:

    def on_form_prefill (self, form, id):
        pass

I have created a patch for these additions and submitted a pull request to the Flask-Admin project.

Then I could override the on_form_prefill method in my SurveyView class as follows:

    def on_form_prefill (self, form, id):
        form.page_list.process_data(
            self.session.query(SurveyPage.page_id)
            .filter(SurveyPage.survey_id == id)
            .order_by(SurveyPage.ordering)
            .all()
        )

And that was the solution to this part of this problem. (In the workaround I actually defined the override of edit_view in a subclass of flask.ext.admin.contrib.sqla.ModelView, because I need the added functionality of that class, but edit_view is normally only defined in flask.ext.admin.model.base.BaseModelView.)

However, at this point I discovered a new problem: while the input was completely stored into the database, the order in which the pages were added to the survey was not preserved. This turned out to be an issue more people walk into with Select2 multiple fields.

---

Addition: fixing the ordering

As it turns out, Select2 cannot preserve order if the underlying form field is a <select>. The Select2 documentation recommends <input type="hidden"> for sortable multiselects, so I defined a new widget type based on wtforms.widgets.HiddenInput and used that instead:

from wtforms import widgets

class Select2MultipleWidget(widgets.HiddenInput):
    """
    (...)

    By default, the `_value()` method will be called upon the associated field
    to provide the ``value=`` HTML attribute.
    """

    input_type = 'select2multiple'

    def __call__(self, field, **kwargs):
        kwargs.setdefault('data-choices', self.json_choices(field))
        kwargs.setdefault('type', 'hidden')
        return super(Select2MultipleWidget, self).__call__(field, **kwargs)

    @staticmethod
    def json_choices (field):
        objects = ('{{"id": {}, "text": "{}"}}'.format(*c) for c in field.iter_choices())
        return '[' + ','.join(objects) + ']'

The data-* attribute is a HTML5 construct to pass arbitrary data in element attributes. Once parsed by JQuery such attributes become $(element).data().*. I here use it to transfer the list of all available pages to the client side.

In order to ensure that the hidden input field becomes visible and behaves like a Select2 field on page load, I needed to extend the admin/model/edit.html template:

{% extends 'admin/model/edit.html' %}

{% block tail %}
    {{ super() }}

    <script src="//code.jquery.com/ui/1.11.0/jquery-ui.min.js"></script>
    <script>
        $('input[data-choices]').each(function ( ) {
            var self = $(this);
            self.select2({
                data:self.data().choices,
                multiple:true,
                sortable:true,
                width:'220px'
            });
            self.on("change", function() {
                $("#" + self.id + "_val").html(self.val());
            });
            self.select2("container").find("ul.select2-choices").sortable({
                containment: 'parent',
                start: function() { self.select2("onSortStart"); },
                update: function() { self.select2("onSortEnd"); }
            });
        });
    </script>
{% endblock %}

As an added bonus, this enables the user to order the widgets that represent the selected pages by drag-and-drop.

At this point, my question is finally fully answered.

---

The code for Select2MultipleField. I suggest you run a diff with flask.ext.admin.form.fields to find the differences.

from wtforms import fields
from flask.ext.admin._compat import text_type, as_unicode

class Select2MultipleField(fields.SelectMultipleField):
    """
        `Select2 <https://github.com/ivaynberg/select2>`_ styled select widget.

        You must include select2.js, form.js and select2 stylesheet for it to
        work.

        This is a slightly altered derivation of the original Select2Field.
    """
    widget = Select2MultipleWidget()

    def __init__(self, label=None, validators=None, coerce=text_type,
                 choices=None, allow_blank=False, blank_text=None, **kwargs):
        super(Select2MultipleField, self).__init__(
            label, validators, coerce, choices, **kwargs
        )
        self.allow_blank = allow_blank
        self.blank_text = blank_text or ' '

    def iter_choices(self):
        if self.allow_blank:
            yield (u'__None', self.blank_text, self.data is [])

        for value, label in self.choices:
            yield (value, label, self.coerce(value) in self.data)

    def process_data(self, value):
        if not value:
            self.data = []
        else:
            try:
                self.data = []
                for v in value:
                    self.data.append(self.coerce(v[0]))
            except (ValueError, TypeError):
                self.data = []

    def process_formdata(self, valuelist):
        if valuelist:
            if valuelist[0] == '__None':
                self.data = []
            else:
                try:
                    self.data = []
                    for value in valuelist[0].split(','):
                        self.data.append(self.coerce(value))
                except ValueError:
                    raise ValueError(self.gettext(u'Invalid Choice: could not coerce {}'.format(value)))

    def pre_validate(self, form):
        if self.allow_blank and self.data is []:
            return

        super(Select2MultipleField, self).pre_validate(form)

    def _value (self):
        return ','.join(map(str, self.data))

Answer 2

This answer related to Dynamic filter on foreignkey in flask-admin, which I think is a very common situation when you would like selection list of field B depends on value of field A etc.

This link might contains helpful information also: https://github.com/flask-admin/flask-admin/issues/797

I have found a solution to this by using on_form_prefill together with query_factory, here are the steps

In the admin definition, override the default implementation of on_form_prefill, and in that method, you can get the current object being edited, so you can define the query_factory of another field based on the current defined field, code shown below:

class ReceivingAdmin(ModelView):
        def on_form_prefill(self, form, id):

        # Get field(purchase_order_id) from the current object being edited via form._obj.purchase_order_id
        if form is not None and form._obj is not None and form._obj.purchase_order_id is not None:
            po_id = form._obj.purchase_order_id
            # Define a dynamic query factory based on current data.
            # Please notice since the po_id parameter need to be passed to the function,
            # So functools.partial is used
            form.lines.form.purchase_order_line.kwargs['query_factory'] =\
                partial(PurchaseOrderLine.header_filter, po_id)

And here is the definition of the query factory in the model:

class PurchaseOrderLine(db.Model):
    @staticmethod
    def header_filter(po_id):
        return AppInfo.get_db().session.query(PurchaseOrderLine).filter_by(purchase_order_id=po_id)

By this way, we could control which record will be shown in the purchase order line list based on parameter po_id, and the value of po_id is passed to the query factory function in on_form_prefill.