Using specific order in find()

Yii 2.0 General Discussions
1

Hello,

I’m trying to use the following PHP statement, however Yii does transform my query, therefore I am not able to use it this way.

What would be the best approach to use such a query?




<?php

$a = Transfer::find()->where(['user_id' => $user->id])->orderBy('end_date IS NULL DESC, end_date DESC, start_date DESC');

?>

The error shown is:

Basically it is interpreting my "end_date IS NULL DESC" as the name of a column, and that is not of course.

Suggestions? I know that I can make use of raw SQL, but I would like another solution if possible.

Thanks

2

Have you tried to put in in array?

http://www.yiiframework.com/doc-2.0/yii-db-querytrait.html#orderBy()-detail

3

Thanks for your answer.

Exactly the same problem, still consdering the IS NULL part as a "column name".

4

Hi,

How does you code look like with the array configuration?

Regards

5

Maybe try


->orderBy([new \yii\db\Expression('end_date IS NULL') => SORT_DESC, 'end_date' => SORT_DESC, 'start_date' => SORT_DESC])
6

It does give an "Illegal offset type".

This is my query:


$a = Transfer::find()->where(['user_id' => $user->id])->orderBy([new \yii\db\Expression('end_date IS NULL') => SORT_DESC, 'end_date' => SORT_DESC, 'start_date' => SORT_DESC]);
7

Looks like only array value can be expression (not key). This works (tested):


->orderBy([new \yii\db\Expression('end_date IS NULL DESC'), 'end_date' => SORT_DESC])
8

Makes sense why it didn’t work :)

It works now, thanks a lot!