karimtabet karimtabet - 1 month ago 23
Python Question

flask admin custom QueryAjaxModelLoader

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.

Answer

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()
    )
Comments