{"id":1628,"date":"2024-07-18T12:26:39","date_gmt":"2024-07-18T11:26:39","guid":{"rendered":"https:\/\/editjournal.redakt.eu\/faxmodem\/?p=1628"},"modified":"2024-07-18T12:26:39","modified_gmt":"2024-07-18T11:26:39","slug":"select-case-then-when","status":"publish","type":"post","link":"https:\/\/editjournal.redakt.eu\/faxmodem\/blog\/development\/cakephp\/quick-bites\/select-case-then-when\/","title":{"rendered":"Complex CASE-THEN-WHEN expressions as columns in select queries"},"content":{"rendered":"

(Below has been tested on CakePHP 5, but should work on 3+.)<\/p>\n

Say you're working on a query that may have to do some currency conversion on the go if user's currency is different from the currency in your database. To make it worse, your site may use a third currency as a base currency (EUR, for example).<\/p>\n

A MySQL query that can do this will look like this:<\/p>\n

SET @base_currency := 'EUR';\nSET @user_currency := 'CHF';\nSELECT\n    CASE Currencies.code\n        WHEN @user_currency THEN Prices.price\n        WHEN @base_currency THEN Prices.price * CurrenciesUser.exchange_rate\n        ELSE Prices.price \/ Currencies.exchange_rate * CurrenciesUser.exchange_rate\n    END AS 'price_converted'\n    \/* all other columns *\/\nFROM\n    prices Prices\n        LEFT JOIN\n    currencies Currencies ON Currencies.id = Pricings.currency_id\n        LEFT JOIN\n    currencies CurrenciesUser ON CurrenciesUser.code = @user_currency\n\/* everything else *\/\nORDER BY price_converted<\/code><\/pre>\n
We start by checking if the user currency matches the currency of a given row - if so, we just return the price. Otherwise, we perform the currency conversion.<\/p>\n
Here's how that will look written as a CakePHP query:<\/p>\n
$user_currency = 'CHF';\n$base_currency = 'EUR';\n$query = $pricesTable->find('all')\n    ->contain([\n        'Currencies',\n        \/\/ ...\n    ])\n    ->join([\n        'CurrenciesUser' => [\n            'type' => 'LEFT',\n            'table' => 'currencies',\n            'conditions' => [\n                'CurrenciesUser.code' => $user_currency\n            ],\n        ],\n    ])\n;\n$query\n    ->selectAlso([\n        'price_converted' => $query->newExpr()->case('Currencies.code')\n            ->when($user_currency)\n                ->then($query->newExpr('Prices.price'))\n            ->when($base_currency)\n                ->then($query->newExpr('Prices.price*CurrenciesUser.exchange_rate'))\n            ->else($query->newExpr('Prices.price\/Currencies.exchange_rate*CurrenciesUser.exchange_rate')),\n    ])<\/code><\/pre>\n
Pay attention to how we use $query->newExpr<\/code> for column names. If we don't, we will get our expressions as literal strings in the query results.<\/p>\n
Sources:<\/p>\n