How do I execute a custom query in model view over "-to-many" relationships in Flask Admin?
I am a beginner with Flask Admin, I have a problem that I cannot solve, even after hours of searching the documentation for a solution.
I changed the name of the entities for better understanding, but this is exactly the same problem.
I have a Many-to-Many relationship (my problem would be the same with a one-to-many relationship) between a User and Entity entity. Skill: A user can have multiple skills, and different users can have the same skills.
The Create (or Publish) Skill Object page contains a field for the Users relationship.
I have over 100,000 users and when I click on the field it is very slow (due to a search in a Javascript script).
To make it faster, I only want to look for "active" users (active is a boolean field in my SQL database) because there are indeed several active users and I will never add a skill to an inactive user.
How can i do this?
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.admin.contrib.sqla import ModelView
from flask.ext.admin import Admin
app = Flask(__name__)
db = SQLAlchemy(app)
admin = Admin(app)
association_table = db.Table(
'user_skill',
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
db.Column('skill_id', db.Integer, db.ForeignKey('skill.id'), primary_key=True)
)
class Skill(db.Model):
name = db.Column(db.String(30), nullable=True)
class User(db.Model):
username = db.Column(db.String(30), nullable=True)
skills = db.relationship(
'Skill',
secondary=association_table,
backref='users'
)
class SkillView(ModelView):
# Something like that
form_relationship_query = {'users': lambda query: query.filter_by(active=True).all()}
admin.add_view(SkillView(Skill, db.session))
source to share
I found a solution:
class SkillView(ModelView):
form_ajax_refs = {
'users': {
'fields': (User.username,)
}
}
This doesn't only display active users, but it does solve my original problem, which was user flight. We now have to enter at least one letter in the User field to trigger the JS request and the html never forces the window to freeze.
source to share
I think you missed the default Flask-Admin filters . So in your case it should be:
class SkillView(ModelView):
def get_query(self):
return self.session.query(self.model).filter(self.model.active == True)
def get_count_query(self):
return self.get_query().count()
source to share
Thanks for your reply.
First, I think what you meant to say:
class UserView(ModelView):
def get_query(self):
return self.session.query(self.model).filter(self.model.active == True)
def get_count_query(self):
return self.get_query().count()
Otherwise, the filter will be applied to the "active" column of the Skill object that does not exist.
This solution works, I've already tried it, but it's a little different from what I'm looking for: indeed, by changing these default filters, I will only see active users in the users view. I want to make sure that all users (active and inactive) in UserView
, but can only select the active user in the user field on SkillView
.
EDIT . After checking, it turned out that using this solution doesn't even solve my problem: not only can I only see the active user in the UserView, but also see all users (active and inactive) in the field in SkillView
, which makes the JS very slow.
source to share