yii2 multiple sort using order case for default order in ActiveDataProvider

Question

I'm creating a website using Yii2 and I would like to sort with multiple field like this:

SELECT * FROM tbl_product t
ORDER BY
CASE
    WHEN t.product_sale_price IS NULL THEN t.product_price
    ELSE t.product_sale_price
END

with query above, this set of data:

Product | product_price | product_sale_price
--------|---------------|-------------------
Prod A  | 2000          | 1200
Prod B  | 1500          | NULL
Prod C  | 1800          | NULL
Prod D  | 1000          | 500

will become:

Product | product_price | product_sale_price
--------|---------------|-------------------
Prod D  | 1000          | 500
Prod A  | 2000          | 1200
Prod B  | 1500          | NULL
Prod C  | 1800          | NULL

How can we put it in defaultOrder of ActiveDataProvider? I tried:

return new ActiveDataProvider([
    'sort' => [
        'defaultOrder' => ['product_sale_price' => SORT_ASC, 'product_price' => SORT_ASC],
        'enableMultiSort' => true,
    ],
]);

return new ActiveDataProvider([
    'sort' => [
        'defaultOrder' => '(CASE WHEN product_sale_price IS NULL THEN product_price ELSE product_sale_price END ASC)',
    ],
]);

Both didn't work. How sholud I do it?

Answer 1

Use for example: https://gist.github.com/XAKEPEHOK/9dfd72d1beb55ddacdcbac6bb61599df

then

$expression= new Expression('(CASE WHEN product_sale_price IS NULL THEN product_price ELSE product_sale_price END ASC)');

return new ActiveDataProvider([
    'sort' => [
        'class'=><here define your sort extend class>
        'defaultOrder' => $expression,
    ],
]);

Or:

$dataProvider = new ActiveDataProvider([
            'query' => $query,            
            'sort'  => [
                'attributes' => [
                    '<attribute that you want sort>' => [
                        'asc' => [new Expression('(CASE WHEN product_sale_price IS NULL THEN product_price ELSE product_sale_price END ASC)')],
                        'desc' => [new Expression('(CASE WHEN product_sale_price IS NULL THEN product_price ELSE product_sale_price END ASC)')],
                   ],
                    // more attribute definitions here
                ],
                'defaultOrder' => ['<attribute that you want sort>' => SORT_DESC],
        ]);