Complex CASE-THEN-WHEN expressions as columns in select queries

in Quick Bites

(Below has been tested on CakePHP 5, but should work on 3+.)

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).

A MySQL query that can do this will look like this:

SET @base_currency := 'EUR';
SET @user_currency := 'CHF';
SELECT
    CASE Currencies.code
        WHEN @user_currency THEN Prices.price
        WHEN @base_currency THEN Prices.price * CurrenciesUser.exchange_rate
        ELSE Prices.price / Currencies.exchange_rate * CurrenciesUser.exchange_rate
    END AS 'price_converted'
    /* all other columns */
FROM
    prices Prices
        LEFT JOIN
    currencies Currencies ON Currencies.id = Pricings.currency_id
        LEFT JOIN
    currencies CurrenciesUser ON CurrenciesUser.code = @user_currency
/* everything else */
ORDER BY price_converted

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.

Here's how that will look written as a CakePHP query:

$user_currency = 'CHF';
$base_currency = 'EUR';
$query = $pricesTable->find('all')
    ->contain([
        'Currencies',
        // ...
    ])
    ->join([
        'CurrenciesUser' => [
            'type' => 'LEFT',
            'table' => 'currencies',
            'conditions' => [
                'CurrenciesUser.code' => $user_currency
            ],
        ],
    ])
;
$query
    ->selectAlso([
        'price_converted' => $query->newExpr()->case('Currencies.code')
            ->when($user_currency)
                ->then($query->newExpr('Prices.price'))
            ->when($base_currency)
                ->then($query->newExpr('Prices.price*CurrenciesUser.exchange_rate'))
            ->else($query->newExpr('Prices.price/Currencies.exchange_rate*CurrenciesUser.exchange_rate')),
    ])

Pay attention to how we use $query->newExpr for column names. If we don't, we will get our expressions as literal strings in the query results.

Sources:

#cakephp-orm