CODEWITHSUNDEEP
sortinggridviewyii2sql-order-bycase
eggnukes
eggnukes

Reputation: 180

Yii2 GridView default order by CASE WHEN

I am using GridView to display some data. I need to add default sorting by 2 columns as follows:

(CASE
    WHEN (inbound_datetime IS NULL AND outbound_datetime < NOW()) OR (inbound_datetime IS NOT NULL AND inbound_datetime < NOW())
        THEN 9999
    WHEN (inbound_datetime IS NOT NULL AND outbound_datetime < NOW())
        THEN inbound_datetime
        ELSE outbound_datetime
END) ASC

If I add this code in the ORDER BY clause in the data provider query, then all of the other sorting options don't work because this condition is applied to the query itself.

I tried to apply the default sorting as I would to any other grid, but it is not working (the method searches for an attribute with that name):

'sort' => [
    'defaultOrder' => [
        '(CASE
            WHEN (inbound_datetime IS NULL AND outbound_datetime < NOW()) OR (inbound_datetime IS NOT NULL AND inbound_datetime < NOW())
                THEN 9999
            WHEN (inbound_datetime IS NOT NULL AND outbound_datetime < NOW())
                THEN inbound_datetime
                ELSE outbound_datetime
        END)' => SORT_ASC,
    ],
],

Is there any way to make this default sorting work and still keep the ability to use grid sorting by column names correctly?

Upvotes: 0

Views: 3079

Answers (1)

eggnukes
eggnukes

Reputation: 180

I did some more thinking and have come up with a solution to this problem.

I define the query in my controller method which opens the view. So I added a condition that checks if sort parameter is passed to the get request. If it is passed, I create the query without the ORDER BY clause. If sort parameter is not passed (no column sorting is done), I create a query with the ORDER BY clause. In my controller method:

if (Yii::$app->request->get('sort')) {
    $query = Travel::find();
} else {
    $query = Travel::find()
        ->orderBy('(CASE
            WHEN (inbound_datetime IS NULL AND outbound_datetime < NOW()) OR (inbound_datetime IS NOT NULL AND inbound_datetime < NOW())
                THEN 9999
            WHEN (inbound_datetime IS NOT NULL AND outbound_datetime < NOW())
                THEN inbound_datetime
                ELSE outbound_datetime
        END) ASC');     
}

Upvotes: 1

Related Questions