October: After removing a column from the list with relation parameter set, error occurs

[robinbonnes]

• OctoberCMS Build: 467
• PHP Version: 7.3.17
• Database Engine: 5.5.41-MariaDB

Description:

Probably an edge case kind of bug. However its always good to report it 🙂

• As soon as you sort a list on a specific column with the following configuration:

    categories:
        label: Categories
        searchable: true
        sortable: true
        relation: categories
        select: name

• And you sort on that column.
• Then remove the column from the list settings
• Produces the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories' in 'order clause' (SQL: select `expenses`.* from `expenses` where `expenses`.`deleted_at` is null order by `categories` desc limit 40 offset 0)
--
  | File: /public_html/website/vendor/laravel/framework/src/Illuminate/Database/Connection.php
  | Line: 664

Steps To Reproduce:

1. Create a plugin with 2 models that are related to each other.
2. Add a list -> column with the following configuration:

    categories:
        label: Categories
        searchable: true
        sortable: true
        export: true
        relation: categories
        select: name

1. Open the backend, sort the list on that column.
2. Go to the list settings (last icon in list header, and deselect the column).

Of course the most easy solution would be to set sortable -> false. However it would be great if it's still possible to sort on a related column.

P.S. Setting sortable -> true on a column that gets overruled with get[Column]Attribute() defined in the model doesn't work. Can we make this work too? I know this would be hard because of pagination, maybe define a custom sort query? 🙂

[LukeTowers]

P.S. Setting sortable -> true on a column that gets overruled with get[Column]Attribute() defined in the model doesn't work. Can we make this work too? I know this would be hard because of pagination, maybe define a custom sort query? 🙂

If what you want is to sort on the final value returned by the getter, that's not going to be easy because sorting runs in the database level. However if you want to be able to sort by the DB value but present a different value to the user that's easy by just using type: partial and rendering whatever data you feel like.

[LukeTowers]

Fixed by https://github.com/octobercms/october/commit/18af35d928956b3ebae636a0996add74d683bec8