October: Backend list column sorting of jsonable element

[ghost]

Hello,

I noticed something strange when you work with backend list with sortable json column.

let's assume you have a "customer" json field like that

{
    firstname: 'John',
    lastname: 'Doe'
}

In your column.yaml you want to output the firstname and lastname in separate columns.

customer[firstname]:
    label: Firstname
    type: text
    searchable: true
    sortable: true
customer[lastname]:
    label: Lastname
    type: text
    searchable: true
    sortable: true

It will works, but the columns are not sortable, even with "sortable" set to "true".

But if I do something like that :

first_name:
    label: Firstname
    type: partial
    searchable: true
    sortable: true
    path: first_name

And in my partial _first_name.htm

<?= json_decode($record->attributes['customer'])->firstname; ?>

My column will be sortable.

The bracket syntax in columns.yaml seems to be the cause of the problem. When you use another name without brackets and manually display the value using partial, it perfectly works.

[NedStrk]

if the column type for customer in your MySQL >= 5.7.8 is set to JSON, than this should work:

    first_name:
        label: Firstname
        type: text
        searchable: true
        sortable: true
        select: 'customer->"$.firstname"'
    last_name:
        label: Lastname
        type: text
        searchable: true
        sortable: true
        select: 'customer->"$.lastname"'

https://dev.mysql.com/doc/refman/5.7/en/json.html

[ghost]

Unfortunately, I use JSON database that doesn't handle that syntax. That's why I think it would need a special function in the october side to find an universal syntax that make this works in every database type. Because I will use this plugin sometime with mysql database, sometimes with JSON. I cannot release a plugin that works with only type of database.

[vosco88]

Hi, I just came accross this issue myself and can confirm when you have jsonable field and accesing it's properties with brackets [], even if you have searchable and sortable set to true it is not working.

The column does not even have the sortable arrow as per below screenshot

[LukeTowers]

There is no support in October currently for DB columns of type JSON. If someone wants to add such support for all DB types supported by October, then feel free to submit a PR. Until then, if you absolutely must have a column be sortable / searchable then I recommend you bring it out into a regular table column.

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in the last 30 days. If this issue is still relevant or you would like to see action on it, please respond and we will get the ball rolling.