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

score 4 · Answer 1 · edited May 23 '17 at 12:25

4

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

edited May 23 '17 at 12:25

Community

1
1

answered Jun 09 '15 at 19:44

Dowst

99
2

get_query is for the global list. He was looking for a way to avoid return skills (manyToMany relationship) not activated – Pyglouthon Oct 12 '17 at 17:03

score 4 · Accepted Answer · answered Mar 12 '16 at 03:41

4

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.

answered Mar 12 '16 at 03:41

Edouard Berthe

1,393
2
18
38

I have similar problem but your solution doesn't help. The model view loads much quicker when I use form_ajax_refs, but the dropdown is empty. Do you why is it happening? – Nurjan Feb 23 '18 at 12:35

score 2 · Answer 3 · edited Apr 11 '16 at 14:42

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.

score 2 · Answer 4 · answered Oct 12 '17 at 17:22

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"]
    ),

}

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

4 Answers4