Hi! I’m having trouble with “case when” type of sorting.
I have a query below:
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? Or perhaps it is not possible?