[FIXED] Cakephp 3 - order by two columns with is null

Issue

Currently, I'm having a problem with sorting on two columns simultaneously in one particular table. In this case, it is required to sort on particular column (modified_timeline) but when this column is null I want to use the timestamp of another column to sort (created). I have the following query:

return $this->find()
        ->contain([
            'Comments' => function ($q){
                return $q
                    ->contain(['Users' => function ($q) {
                        return $q->select($this->select);
                    }])
                    ->order(['Comments.created' => 'ASC']);
            },
            'Users' => function ($q) {
                return $q->select($this->select)
                        ->contain(['Pictures' => function ($q) {
                            return $q->where(['Pictures.profile_photo' => true]);
                        }]);
            },
            'Albums'
        ])
        ->matching('Albums.Users', function ($q) use ($userId) {
            return $q->where(['Users.id' => $userId]);
        })
        // This particular line:
        ->order([
            'Pictures.modified_timeline' => 'DESC',
            'Pictures.created' => 'ASC'
        ]);

This works only works partially in a way that it doesn't take into account that the modified_timeline can be null. I was thinking to use a case expression to solve this problem but I'm not really understanding how to apply the isNull() function inside a case expression. The following SQL code explains what I want:

ORDER BY
CASE
  WHEN Pictures.modified_timeline is null
  THEN Pictures.created
  ELSE Pictures.modified_timeline
END

My question therefore is how to create the SQL code above with the Cakephp 3 query builder?

Solution

This query produce the sql as yo mentioned above :

 ->order(function ($exp, $q) {
      return $exp->addCase(
        [
            $q->newExpr()->eq('modified_timeline', null),
            //$q->newExpr()->gt('modified_timeline', 0), // or you may change condition here
       ],
       ['Pictures.created','Pictures.modified_timeline'], // values matching conditions
       ['string', 'string'] // type of each value (optional)
   );

For more details : see cakephp documentation.

Answered By - Manohar Khadka