Custom sort function for column in backend list

Need Help
#1

Hi guys,
I encounter one problem that I haven’t been able to figure out yet.

My client is storing some kind of a data that they then disclosure on a website. Problem is that they have their own format of one field for marking single items and they need sorting according to this field in the backend list.

Is it possible to somehow define some custom sorting function to achieve this? Or is there any other way? Sorry in a advance if it is kind of lame question. I am not very skilled as a programmer and I didn’t found any solution in docs or here.

PS.: Format of the field is basically number/year and the value is stored as a string. I need to sort it by the years and by the number in the year accordingly.

Thank you in advance

#2

Just to make sure:

In your table, you have a field let’s called it “custom_field” - type varchar or text.

In your field custom_field, you have some values like “123_2023”, “1923_2020”,

In your list, it looks like this and you would like to sort like this:

image

if this is correct, i’m wondering if gathering both information is one field is the right way ? could they want to sort either by the numbers or the year? wouldn’t be better to get two different fields/columns?

#3

Yeah, that’s exactly how it is.

Thing is, that I’m migrating the information from the old web and they’ve been using this format for more then 10 years.

So, yeah, I’m still considering parsing those values into two seperate fields. I was just wondering, if I can write some custom compare function for sorting, that would handle it.

#4

Ok so if you don’t split it, you would have to make a selector so the person can chose if he wants to filters on number or on the date for that column…

I’ve never took this approach.

Maybe a checkbox in the filters could be a quick solution. For example (not tested at all) :

\Event::listen('backend.list.extendQueryBefore', function ($listWidget, $query) {
            // $filterByYear being your input that you can find in $listWidget
            // Order by custom_field
            if ($filterByYear) {
                return $query->orderByRaw("SUBSTRING_INDEX(custom_field, '_', -1) ASC");
            } else {
                return $query->orderBy('custom_field', 'ASC');
            }
        });

To manage also ASC and DESC, you could put a dropdown or another checkbox…

1 Like
#5

Thank you for suggestion.

I already split it to two values and used native sorting capabilities of the backend list view.

But still I’m not satisfied with this solution, as I force the client to use new way of storing the values, which also happens to be problem as the old format is used in export from the other software and chances they will change it is close to zero :slight_smile: .

I did some more searching on the topic and I found that I can define custom column type [List Controller - October CMS - 3.x]. I was wondering if I am not able to define some compare function for sorting of custom column type. Is it really possible to sort list columns only by number or string?

My sorting would actually work if I put the year first and then use number of the item with fixed length. So for example 1/2023 would turn into 20230001.

What I’m trying to achieve is that client can still use this old format (it’s in the export from other software), but the backend list would be able to translate it to the new one and use it for sorting under hood.

Is there any better way to achieve this? I am thinking about something like defining compare function for sorting for custom column type. Is it possible to do something like this?

#6

You will need to register the custom sort function with SQL to preserve pagination features, since these occur at the SQL level.

Otherwise, custom sorting in memory can happen without pagination with the listExtendRecords override.

public function listExtendRecords($records)
{
    return $records->sortBy('foobar');
}