Filter by Day of Week in Flask-Admin

Question

The DB Model is

class DoctorWaitTimes(db.Model):
    __tablename__ = 'doctorwaittimes'

    id = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime)
    waittime = db.Column(db.Integer)

Then in the ModelView Class

column_filters = ['datetime']

This allows the user to select

• equals
• not equals
• greater than
• smaller than
• between
• not between
• empty

and a clickable calendar is displayed for ease of entry of dates/times. This is perfect however there are two changes I would like to make.

I would like to remove the following options:

• not equals
• greater than
• smaller than
• not between
• empty

but I can't find anything in the docs. Secondly, I would like to be able to filter by day of the week. So for example it will only display every Tuesday.

column_filters = [
        FilterTerminal(
            DoctorWaitTimes.datetime, 'Day of Week', 
            options=(('1', 'Monday'), ('2', 'Tuesday'))
        ), 'datetime'
    ]

However, I am unsure how to get the actual filter to work. In MySQL you can run

SELECT * FROM doctorwaittimes WHERE AND DAYOFWEEK(datetime) = 1; 

to select Mondays, but I am not sure how to do it using DB Model.

Answer 1

To remove several types of filters from your view use FilterConverter class. It has several lists of filters applicable to each type of column. Subclass it and replace these lists to remove undesired filters. Specify the new class as filter_converter attribute of your view:

from flask_admin.contrib.sqla import ModelView, filters

class DoctorFilterConverter(filters.FilterConverter):
    datetime_filters = (
        filters.DateTimeEqualFilter,
        filters.DateTimeBetweenFilter
    )

class DoctorView(ModelView):
    filter_converter = DoctorFilterConverter()
    column_filters = ['datetime']

You can use the same way to add new filters. Day-of-the-week filter can be implemented this way:

class DayOfWeekFilter(filters.BaseSQLAFilter):
    def __init__(self, column, name, options=None, data_type=None):
        options = (
            ('1', filters.lazy_gettext(u'Monday')),
            ('2', filters.lazy_gettext(u'Tuesday')),
            ('3', filters.lazy_gettext(u'Wednesday')),
            ('4', filters.lazy_gettext(u'Thursday')),
            ('5', filters.lazy_gettext(u'Friday')),
            ('6', filters.lazy_gettext(u'Saturday')),
            ('7', filters.lazy_gettext(u'Sunday'))
        )
        super(DayOfWeekFilter, self).__init__(column, name, options, data_type)

    def operation(self):
        return filters.lazy_gettext('day of week')

    def validate(self, value):
        return value in ('1', '2', '3', '4', '5', '6', '7')

    def apply(self, query, value, alias=None):
        return query.filter(db.func.extract('dow', self.get_column(alias)) == value)

Then you can add it to datetime_filters:

class DoctorFilterConverter(filters.FilterConverter):
    datetime_filters = (
        filters.DateTimeEqualFilter,
        filters.DateTimeBetweenFilter,
        DayOfWeekFilter
    )

Note that there is no universal SQL function to extract day of the week. I used PostgreSQL EXTRACT function. In MySQL you can try to use WEEKDAY function:

def apply(self, query, value, alias=None):
    return query.filter(db.func.weekday(self.get_column(alias)) == value)

And you'll need to change the numeration of options as WEEKDAY function returns 0 for Monday and 6 for Sunday.