yii2 多重排序，使用活动数据提供程序中默认顺序的顺序大小写

Question

我正在使用 Yii2 创建一个网站，我想使用多个字段进行排序，如下所示：

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

通过上面的查询，这组数据：

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

将成为：

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

我们如何将其置于 ActiveDataProvider 的默认顺序中？我试过了：

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)',
    ],
]);

两者都没有用。我做得有多笨？

Answer 1

例如使用：https://gist.github.com/XAKEPEHOK/9dfd72d1beb55ddacdcbac6bb61599df

然后

$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,
    ],
]);

或：

$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],
        ]);