flask admin custom QueryAjaxModelLoader

Question

From what I understand, Flask Admin supports AJAX use for foreign key model loading. The Flask Admin - Model Documentation covers the basics under the heading form_ajax_refs. I have managed to use this successfully on many occasions, however I am having issues with the level of customisation that I hope to achieve. Let me elaborate.

I have a Product model, an Organisation model and a join table to relate them, defined as so:

class Product(Base):
    __tablename__ = "products"

    product_uuid = Column(UUID(as_uuid=True), primary_key=True)
    title = Column(String, nullable=False)
    description = Column(String, nullable=False)
    last_seen = Column(DateTime(timezone=True), nullable=False, index=True)
    price = Column(Numeric(precision=7, scale=2), nullable=False, index=True)

class Organisation(Base):
    __tablename__ = "organisations"
    org_id = Column(String, primary_key=True)
    org_name = Column(String, nullable=False)
    products = relationship(
        Product,
        secondary="organisation_products",
        backref="organisations"
    )

organisation_products_table = Table(
    "organisation_products",
    Base.metadata,
    Column("org_id", String, ForeignKey("organisations.org_id"), nullable=False),
    Column("product_uuid", UUID(as_uuid=True), ForeignKey("products.product_uuid"), nullable=False),
    UniqueConstraint("org_id", "product_uuid"),
    )

In a Flask Admin Model view of a model called CuratedList that has a foreign key constraint to the Product model, I am using form_ajax_refs in the form create view, to allow selection of dynamically loaded Product items.

form_ajax_refs = {"products": {"fields": (Product.title,)}}

This works nicely to show me ALL rows of the Product model.

My current requirement, however, is to only use the AJAX model loader to show products with a specific org_id, for example "Google".

Attempt No. 1

Override get_query function of the ModelView class to join on organisation_products_table and filter by org_id. This looks something like this:

def get_query(self):
    return (
        self.session.query(CuratedList)
        .join(
            curated_list_items_table,
            curated_list_items_table.c.list_uuid == CuratedList.list_uuid
        )
        .join(
            Product,
            Product.product_uuid == curated_list_items_table.c.product_uuid
        )
        .join(
            organisation_products_table,
            organisation_products_table.c.product_uuid == Product.product_uuid
        )
        .filter(CuratedList.org_id == "Google")
        .filter(organisation_products_table.c.org_id == "Google")
    )

Unfortunately, this does not solve the issue, and returns the same behaviour as:

def get_query(self):
    return (
        self.session.query(CuratedList)
        .filter(CuratedList.org_id == self._org_id)
    )

It does not affect the behaviour of form_ajax_refs.

Attempt No.2

The Flask Admin - Model Documentation mentions another way of using form_ajax_refs, which involves using the QueryAjaxModelLoader class.

In my second attempt, I subclass the QueryAjaxModelLoader class and try to override the values of it's model, session or fields variables. Something like this:

class ProductAjaxModelLoader(QueryAjaxModelLoader):
    def __init__(self, name, session, model, **options):
        super(ProductAjaxModelLoader, self).__init__(name, session, model, **options)

        fields = (
            session.query(model.title)
            .join(organisation_products_table)
            .filter(organisation_products_table.c.org_id == "Google")
        ).all()

        self.fields = fields
        self.model = model
        self.session = session

And then instead of the previous form_ajax_refs approach, I use my new AjaxModelLoader like so:

form_ajax_refs = {
    "products": ProductAjaxModelLoader(
        "products", db.session, Product, fields=['title']
    )
}

Unfortunately, whether overriding the values of session or model with my query returns no products from the AJAX loader, and overriding fields still returns all products; not just products of org_id "Google".

What I Hope to Not Resort to

I would like to be able to achieve this without having to create a new model for each org, as this will prove to be non-scalable and of bad design.

Any suggestions welcomed. Thanks.

Svetlana

Here's how AjaxModelLoader class looks like: github.com/flask-admin/flask-admin/blob/master/flask_admin/… All you have to do is to override get_list method and apply your filters using this as an foundation: github.com/flask-admin/flask-admin/blob/master/flask_admin/… – Whish

Nice question. Would love it if flask-admin incorporated a generic way of constraining a field value depending on another field's value. +1 – Incumber

Answer 1

Thanks to Joes comment to my original question, I have formulated a working solution:

Override AjaxModelLoader function get_list like so:

def get_list(self, term, offset=0, limit=DEFAULT_PAGE_SIZE):
    filters = list(
        field.ilike(u'%%%s%%' % term) for field in self._cached_fields
    )
    filters.append(Organisation.org_id == "Google")
    return (
        db.session.query(Product)
        .join(organisation_products_table)
        .join(Organisation)
        .filter(*filters)
        .all()
    )

Svetlana

Any idea how to do this based on using another field rather than a static string ('Google' above), i.e. i select organisation in one field and then based on that the dropdown for products is able to filter based on the id for example. – Lynxeyed

It would be a lot more feasible to first store the selected field in the model, as opposed to having the drop down dynamically populate with the contents of other fields in the same form. In this case, I would create a separate model view to store organisations. Otherwise, you might be looking at doing some custom Javascript. – Svetlana

I thought it would be, i was hoping you might have needed to and had already done the hard work :) I'll have a go at making a generic solution as i need this for my administrative portal in several different contexts. – Lynxeyed

I managed to achieve this quite generically i think. From QueryAjaxModelLoader i modified init to accept and additional filter arg, i also overrode get_list to and_ the filter. To generate the form i had to modify AjaxSelect2Widget call to ensure ensure my filter field got added to the generated html. Finally i modified form-1.0.0.js processAjaxWidget to pass back the filter info as part of the query, – Lynxeyed

@Lynxeyed If you were able to share a gist that would be tremendously helpful. Constraining field values based on the value of another field must be a widespread need. – Incumber

Sure, i'll try and put something up later – Lynxeyed

@Incumber I think my answer it about what you were talking about posting... https://mcmap.net/q/936752/-flask-admin-custom-queryajaxmodelloader – Kneepad

Answer 2

After much trial and error, and thanks to the posts above, I present a generic way to pass in filters to the Ajax Model Loader.

Here is a generic class that enables filtering on the foreign key table.

from flask_admin.contrib.sqla.ajax import QueryAjaxModelLoader, DEFAULT_PAGE_SIZE

class FilteredAjaxModelLoader(QueryAjaxModelLoader):
    additional_filters = []

    def get_list(self, term, offset=0, limit=DEFAULT_PAGE_SIZE):
        filters = list(
            field.ilike(u'%%%s%%' % term) for field in self._cached_fields
        )
        for f in self.additional_filters:
            filters.append(f)
        # filters.append(User.list_id == 2) # Now this is passed in the constructor
        # filters.append(User.is_active == 'Y')
        return (
            db.session.query(self.model)
                .filter(*filters)
                .all()
        )

    def __init__(self, name, session, model, **options):
        super(FilteredAjaxModelLoader, self).__init__(name, session, model, **options)
        self.additional_filters = options.get('filters')

Usage:

Pass it into form_ajax_refs like you would QueryAjaxModelLoader

FilteredAjaxModelLoader('component', db.session, User, fields=['list_value'],
                                filters=[User.list_id == 2, User.is_active == 'Y'])

Hope that helps.

Kneepad