How to do a custom query in Model View on *-to-Many relationship in Flask Admin?

Question

I begin with Flask Admin, I have a problem that I cannot solve, even after several hours looking for a way to solve it in the documentation.

I changed the name of the entities for more understanding, but it's exactly the same problem.

I have a Many-To-Many relationship (my problem would be the same with a One-To-Many relationship) between an entity User and an entity Skill : a User can have several skills, and different users can have the same skills.

On the page of creation (or edition) of the entity Skill, there is a field for the "Users" relationship.

I have more than 100 000 Users, and when I click on the field it's really slow (due to the Javascript search script).

To make it faster, I only want to search into the 'active' Users (active is a boolean field in my SQL Database), because there are really a few active Users, and I will never add skill to an inactive User.

How can I do that ?

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

Answer 1

I have found the solution :

class SkillView(ModelView):

    form_ajax_refs = {
        'users': {
            'fields': (User.username,)
        }
    }

This doesn't display only the Active Users, but it solves my initial problem which was the User field performances. Now, we have to enter at least one letter in the User field to launch a JS request, and the html never makes the window freeze.

Answer 2

I think you missed Flask-Admin default 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()

Answer 3

Thank you for your answer.

First, I think that you wanted 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()

Or else, the filter will apply on the column "active" of the entity Skill, which doesn't exist..

That solution is working, I had already tried it, but it's a bit different from what I'm looking for : indeed, by changing these default filters, I will only see the active Users in the Users View. What I want is keeping seeing all the Users (active and inactive) in the UserView, but only be able to select an active User in the User Field on the SkillView.

EDIT : After verification, it appears that using this solution doesn't even solve my problem : not only I see only active User in the UserView, but also I see all the Users (active and inactive) in the field in the SkillView, which makes the JS very slow.

Answer 4

You can improve it by using the ajax loader like below:

form_ajax_refs = {
    'users': QueryAjaxModelLoader(
        'users',
        db.session,
        User,
        fields=['username'],
        page_size=10,
        filters=["active=True"]
    ),

}