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.
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()
)
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.
链接地址: http://www.djcxy.com/p/86244.html上一篇: 重命名表中删除空格的所有列