Use a “phrase search” in flask admin

Question

I am using Flask-Admin and I am very happy with it.

The docs show several options that can be used to search predefined fields. I want to allow my users to use quotation marks to search, as for instance, in:

"some phrase of which the order should be intact"

How can I do this with Flask-Admin?

Answer 1

In Flask-Admin 1.3.0 you can override the _apply_search method in your admin view class. See the very simple code example below - mostly taken from the Flask-Admin sqla example app.py.

Essentially, you want to generate an SQL like %Your Phrase Here% for the columns in your column_searchable_list. The standard behaviour of _apply_search is to split the search input text on spaces and generate SQL like fragments for each term in the resultant array.

Instead, you can use a regular expression to see if a quoted string has been entered, extract the phrase within the quotes, create the appropriate SQL like fragment and then pass that on to the code that generates the query.

You can also do something similar to implement phrase filtering - see the class FilterPhrase in the code below and how it is used in the column_filters list definition.

For more sophisticated phrase searching you could use something like Whoosh, the built-in phrase searching capabilities of Postgres text searching (maybe combined with SQLAlchemy-Searchable) or even Elasticsearch.

# coding: utf-8
__author__ = 'Paul'

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import or_
from flask.ext.admin import Admin
from flask.ext.admin.contrib.sqla import ModelView
from flask.ext.admin.babel import lazy_gettext
from flask.ext.admin.contrib.sqla.filters import BaseSQLAFilter
import re

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)


@app.route('/')
def index():
    return '<a href="/admin/">Click me to get to Admin!</a>'


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))
    content = db.Column(db.Text, nullable=False)

    def __unicode__(self):
        return self.title


class FilterPhrase(BaseSQLAFilter):
    def apply(self, query, value, alias=None):
        stmt = "%{phrase}%".format(phrase=value)
        return query.filter(self.get_column(alias).ilike(stmt))

    def operation(self):
        return lazy_gettext('phrase')


class PostAdmin(ModelView):
    column_searchable_list = ['title', 'content']

    column_filters = (
        FilterPhrase(Post.title, "Title"),
        FilterPhrase(Post.content, "Content"),
    )

    def _apply_search(self, query, count_query, joins, count_joins, search):

        phrases = re.findall(r'"([^"]*)"', search)

        if len(phrases) == 0:
            return super(PostAdmin, self)._apply_search(query, count_query, joins, count_joins, search)

        stmt = "%{phrase}%".format(phrase=phrases[0])

        # The code below is taken directly from the base _apply_search
        filter_stmt = []
        count_filter_stmt = []

        for field, path in self._search_fields:
            query, joins, alias = self._apply_path_joins(query, joins, path, inner_join=False)

            count_alias = None

            if count_query is not None:
                count_query, count_joins, count_alias = self._apply_path_joins(count_query,
                                                                               count_joins,
                                                                               path,
                                                                               inner_join=False)

            column = field if alias is None else getattr(alias, field.key)
            filter_stmt.append(column.ilike(stmt))

            if count_filter_stmt is not None:
                column = field if count_alias is None else getattr(count_alias, field.key)
                count_filter_stmt.append(column.ilike(stmt))

        query = query.filter(or_(*filter_stmt))

        if count_query is not None:
            count_query = count_query.filter(or_(*count_filter_stmt))

        return query, count_query, joins, count_joins


# Create admin
admin = Admin(app, name='Phrase Searching')
admin.add_view(PostAdmin(model=Post, session=db.session, category='Blog', name='Posts'))


def build_db():
    sample_posts = [
        {
            'title': "de Finibus Bonorum et Malorum - Part I",
            'content': "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut"
        },
        {
            'title': "de Finibus Bonorum et Malorum - Part II",
            'content': "Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque"
        },
        {
            'title': "de Finibus Bonorum et Malorum - Part III",
            'content': "At vero eos et accusamus et iusto odio dignissimos ducimus qui blanditiis praesentium"
        }
    ]

    db.drop_all()
    db.create_all()

    for row in sample_posts:
        post = Post(**row)
        db.session.add(post)

    db.session.commit()


if __name__ == '__main__':
    build_db()
    app.run()