Sort/search on custom attributes

[johanboekhoven]

Am I correct to assume we can't do search and sort on custom attributes?
https://aminalaee.dev/sqladmin/cookbook/display_custom_attributes/

for custom attribute : 'client_id'

column_list=[models.Task.id, 'client_id'] ✔
column_sortable_list=[models.Task.id, 'client_id'] ❌
column_searchable_list=[models.Task.id, 'client_id'] ❌

Is there logic that I can implement to make that work too, something in the list_query perhaps?

Thanks, would love to have custom attributes searchable and sortable.

[aminalaee]

hey @johanboekhoven ,
I think the short answer is that custom attribute is not very clear. for example the custom attribute might be a Python @Property on the model which is calculated on the runtime, it's the same for both search and sort fields.
I think right now you can override the search_query to add your logic there, but for the sort I have to admit it's more complicated.

It might help if you paste a very minimal example here,

[johanboekhoven]

Thanks @aminalaee

Gladly give you a small example.

Here's the minimal model and minimal view, with the custom property (as you said @Property) that get's the asset.client_id field)

class Task(Base):
    __tablename__ = "tasks"

    id = Column(Integer, primary_key=True)
    asset_id = Column(Integer, ForeignKey("assets.id"))
    task_type = Column(String, nullable=True)

    asset = relationship("Asset", back_populates="tasks")

    @property
    def client_id(self):
        if self.asset is None:
            return ""
        return self.asset.client_id



class TaskView(ModelView, model=Task):

    column_list = [
        models.Task.id,
        models.Task.asset_id,
        models.Task.task_type,
        models.Task.asset,
        "client_id",  # works fine ✔
    ]

    column_sortable_list = [
        models.Task.id,
        models.Task.asset_id,
        models.Task.task_type,
        models.Task.asset,
        "client_id",  # fails after pressing on column ❌
    ]
    column_searchable_list = [
        models.Task.id,
        models.Task.asset_id,
        models.Task.task_type,
        models.Task.asset,
        "client_id", # fails immediatly ❌
    ]

Hopefully it gives a bit more insight.

[johanboekhoven]

If you could give a hint on the search query that would be great already, I do have more use for search then sort right now.

[johanboekhoven]

@aminalaee The problem is the custom attributes have no .key property correct in _search_fields, so the logic should detect the type of prop in _search field is a custom attribute and have it's own routine to create an expression. Would that be enough?

I'm also playing with just joining 2 tables for this view, which for query works fine, but I'm unsure if I can can use 2 models to define a column list. Say I join Asset on Task, how do I declare the column list, can I feed multiple models to the view somehow?

[aminalaee]

Yeah exactly, that's why I suggested that you override the search method to do any logic you want:

class TaskView(...):
    def search_query(self, stmt, term):
        return stmt.filter(Task.asset.isnot(None))  # just a simple example

But as you can see this is very limiting, and the issue is not with the SQLAdmin, SQLAlchemy allows defining custom attributes properly:
https://docs.sqlalchemy.org/en/20/orm/extensions/hybrid.html

TBH I haven't tested this with SQLAdmin but as you can see, if you define a hybrid_property you can use that in your queries, there might be some changes required in SQLAdmin in order to make it work, but my guess is that sorting by this property will not be possible, but search might be possible with a few changes.

[johanboekhoven]

@aminalaee Ok, I have the best solution possible in my situation I think!

class TaskView(ModelView, model=Task):

    column_default_sort = (models.Task.id, True)  # colomn, desc

    list_query = select(models.Task).join(models.Task.asset)

    column_list = [
        models.Task.id,
        models.Asset.client_id,

    column_sortable_list = [
        models.Task.id,
        models.Asset.client_id,
    
    column_searchable_list = [
        models.Task.id,
        models.Asset.client_id,

class Task(Base):
    __tablename__ = "tasks"

    id = Column(Integer, primary_key=True)

    @property
    def client_id(self):
        if self.asset is None:
            return ""
        return self.asset.client_id

So the basic join on the list_query suddenly makes everything very transparent, and best yet, I don't need custom attributes for this. This is probably common knowledge, but if you agree I think this would warrent a small place in the docs somewhere. For now I should only use ca's for displaying meta data type fields. Joins is where the easy gains are.

*edit: in order for viewing the property you need to declare it as property, I forgot the ca's where still there when writing this message..

[aminalaee]

Sure, glad if it helps you and someone else too 👍