How do i use flask-admin column_sortable_list with a database relationship

Question

I'm making a flask website that uses flask-admin for database management. I am trying to sort a column that contains a foreign key.

One of my flask-admin model views is for an intermediate table, i would like to sort the table on my view by a column that is a relationship to another table. for some reason column_default_sort = 'pizza_id' sorts the list but column_sortable_list = ('pizza_id',) does not work

here is how it looks currently what i would like is for the Pizza table header to be blue like it is for Id meaning it can click it and it will sort by that column. an example is given below

here are my sqlalchemy models

class Topping(db.Model):
    __tablename__ = 'toppings'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False, unique=True)
    description = db.Column(db.Text)
    price = db.Column(db.Float, nullable=False)

    def __repr__(self):
        return f"{self.name}"


class Pizza(db.Model):
    __tablename__ = 'pizza'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False, unique=True)
    description = db.Column(db.String)
    extra_price = db.Column(db.Float)
    toppings = db.relationship('Topping', secondary='pizza_toppings')

    def __repr__(self):
        return self.name


# my pizza intermediate table
class PizzaTopping(db.Model):
    __tablename__ = 'pizza_toppings'

    id = db.Column(db.Integer, primary_key=True)

    pizza = db.relationship('Pizza', primaryjoin='PizzaTopping.pizza_id == Pizza.id', backref='pizza_toppings')
    topping = db.relationship('Topping', primaryjoin='PizzaTopping.topping_id == Topping.id', backref='pizza_toppings')

    pizza_id = db.Column(db.ForeignKey('pizza.id'), nullable=False)
    topping_id = db.Column(db.ForeignKey('toppings.id'), nullable=False)

here is my flask-admin model view

class MyModelView(ModelView):
    can_set_page_size = True
    page_size = 15
    column_display_pk = True
    column_display_all_relations = True
    column_sortable_list = ('pizza_id',)
    column_default_sort = 'pizza_id'
    can_export = True

    def is_accessible(self):
        return current_user.is_authenticated and current_user.has_roles(("owner", 'admin'))

    def inaccessible_callback(self, name, **kwargs):
        return redirect('/')

Any help is greatly appreciated and if you need more information please ask.

Answer 1

Use the dotted relationship attribute. e.g. to sort by pizza name or topping name:

class MyModelView(ModelView):

    # ...
    column_sortable_list = ('pizza.name', 'topping.name', 'id')
    # ...