Ordering asc then nulls in CakePHP pagination

Question

I have this code in cakePHP

$this->paginate = [
        'sortWhitelist' => [
            'Companies.name',
            'Grants.shortname',
            'amount',
            'contact',
            'LatestHistory.Histories__deadline',
            'Statuses.name'
        ],
        'order' => [
            'LatestHistory.Histories__deadline' => 'ASC',
            'Statuses.await' => 'DESC',
            'Statuses.name'
        ]
    ];

It orders by Histories deadline ASC, but the nulls come first, than the another records, where Histories deadline is not null. I would like, if the ASC list would be in the first place, than the nulls. How can I resolve this issue?

Thanks

Please always mention your _exact_ CakePHP version (last line in `vendor/cakephp/cakephp/VERSION.txt` or `lib/Cake/VERSION.txt`) - thanks! — ndm, Nov 10 '17 at 11:57

score 0 · Answer 1 · answered Nov 10 '17 at 13:34

0

Try the following:

$this->paginate = [
    'sortWhitelist' => [
        'Companies.name',
        'Grants.shortname',
        'amount',
        'contact',
        'LatestHistory.Histories__deadline',
        'Statuses.name'
    ],
    'order' => [
        '-LatestHistory.Histories__deadline' => 'DESC',
        'Statuses.await' => 'DESC',
        'Statuses.name'
    ]
];

Pay attention to the minus sign in front of LatestHistory.Histories__deadline and the DESC reversed order .

This technique is described in MySQL Orderby a number, Nulls last.

answered Nov 10 '17 at 13:34

Inigo Flores

4,461
1
15
36

This is most likely to break when automatic identifier quoting is enabled, as the sign would be treated as being part of the identifier. Using a `CASE` expression is probably a better, more robust (and also cross DBMS compatible) solution. – ndm Nov 10 '17 at 13:43
Yeah, you are right. I posted the quick solution, and was later going to expand my answer to add other options, but @drmonkeyninja beat me to it :) The above works for MySQL (specified by the OP), but will break with `'quoteIdentifiers' => true` (confirmed). So it will probably not work for CakePHP 2.x (haven't tested it though). – Inigo Flores Nov 10 '17 at 21:01

drmonkeyninja · Answer 2 · 2017-11-12T12:27:37.750

You can do this by using two order by conditions in your query:-

ORDER BY LatestHistory.Histories__deadline is null, LatestHistory.Histories__deadline ASC

The first order by works by ordering the statement by whether it returns true or false.

So your Cake paginate query would look like:-

$this->paginate = [
    'sortWhitelist' => [
        'Companies.name',
        'Grants.shortname',
        'amount',
        'contact',
        'LatestHistory.Histories__deadline',
        'Statuses.name'
    ],
    'order' => [
        'LatestHistory.Histories__deadline is null',
        'LatestHistory.Histories__deadline' => 'ASC',
        'Statuses.await' => 'DESC',
        'Statuses.name'
    ]
];

Alternatively use CASE WHEN:-

ORDER BY CASE WHEN LatestHistory.Histories__deadline IS NOT NULL THEN 1 ELSE 2 END, LatestHistory.Histories__deadline ASC

This is probably a better answer. However, you may want to use `LatestHistory.Histories__deadline` => `ASC` instead. Also, change it to `LatestHistory.Histories__deadline ASC` on your second option. — Inigo Flores, Nov 10 '17 at 21:04
Thanks @InigoFlores I missed that the OP wanted ascending order, I've updated accordingly. — drmonkeyninja, Nov 12 '17 at 12:28

Ordering asc then nulls in CakePHP pagination

2 Answers2