Custom and sortable column in Flask-Admin

Question

I'm using Flask-Admin and SQLAlchemy and struggle to create a custom, sortable field in the listview. I a User and a Photo model like this:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    photos = db.relationship('Photo', backref='user', lazy='dynamic', cascade='all, delete-orphan')

class Photo(db.Model):
    __tablename__ = 'photos'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
    filename = db.Column(db.String(128))
    publish = db.Column(db.Boolean, index=True)
    date = db.Column(db.DateTime, default=datetime.now)

I then create a custom View (UserView), create a custom field called 'test' and use a column_formatters to rewrite it to include the count of photos. While the result is correct, the field isn't sortable. Is there another way to solve this?

class UserView(sqla.ModelView):

    column_list = ('username', 'test')

    def _count_formatter(view, context, model, name):
        return model.photos.count()

    column_formatters = {
        'test': _count_formatter
    }

    def is_accessible(self):
        return login.current_user.is_authenticated

Answer 1

You can use SQLAlchemy's Hybrid Attributes which are useful for defining high level functions acting on a model class.

The modified model:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import select, func

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    photos = db.relationship('Photo', backref='user', lazy='dynamic', cascade='all, delete-orphan')

    @hybrid_property
    def number_of_photos(self):
        return self.photos.count()

    @number_of_photos.expression
    def number_of_photos(cls):
        return select([func.count(Photo.id)]).where(Photo.user_id == cls.id).label('number_of_photos')

We've just added the new hybrid attribute. We can access it like a normal table column on a User instance: user.number_of_photos.

And the modified UserView:

class UserView(sqla.ModelView):
    column_list = ('username', 'number_of_photos')
    column_sortable_list = ['username', 'number_of_photos']

    def is_accessible(self):
        return login.current_user.is_authenticated

Here we have to define explicitly the list of sortable/visible columns.