After Upgrade to CakePHP4 sorting dates no longer work

Need Help
1

Hi. I’ve upgraded an old CakePHP3 app to 4.4. Was a lot of work but most things work now. My biggest problem is the sorting function of the paginator in the index actions. If i have a date field and want to sort it, CakePHP generates SQL that is no longer valid for modern MySQL versions (8+).

For example it generates this order by clause:

ORDER BY if(Containers.date = '' or Containers.date is null, 1, 0), Containers.date, number ASC, `Containers`.`date` asc

Where this part is the culprit:

Containers.date = ''

It causes this MySQL error:

SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’

How can i fix this?

2

How is your php-code looks like ?
Are you using FrozenTime?

3

No, i’m not using FrozenTime. It’s just the normal Link from the Cake3 scaffolding:

            <th><?= $this->Paginator->sort('date', 'Datum') ?></th>
4

I also cannot sort any other field. If i sort for the customer name for example, i receive this error:

strpos(): Argument #1 ($haystack) must be of type string, int given📋

It’s in file CORE/src/DataSource/Paging/NumericPaginator.php line 614:

 protected function _removeAliases(array $fields, string $model): array
    {
        $result = [];
        foreach ($fields as $field => $sort) {
            if (strpos($field, '.') === false) {
                $result[$field] = $sort;
                continue;
            }

This exact line

            if (strpos($field, '.') === false) {

The $field variable holds just “date” in this example. I wonder why, because i wanted to sort for customer name. It seems like a multidimensional array is expected, but not given.

Maybe this has got something to to that the application used a really old version of the friendsofcake/search plugin which i had to upgrade to the latest version?

5

Since you are unable to sort any field. Did you set the sortableFields for paginator ?

I am not familiar with the search-plugin, as I do the search myself using Paginator. It works for me in Cake 3 & Cake 4. I would suggest trying out a simple no-plugin of Paginator to see whether it works.