DB CASE WHEN in SELECT

[mariuszkrzaczkowski]

New feature, adding the possibility to use CASE WHEN in yii\db\Query

DB support for CASE WHEN

• MySQL
• PostgreSQL
• MSSQL
• Oracle
• SQLite
• Cubrid

TODO

• Choose syntax for it
• Write tests
• Implement

[alex-code]

This is already possible using an Expression

->select(['c' => new \yii\db\Expression("CASE WHEN 1>0 THEN 'true' ELSE 'false' END")])->

[samdark]

It's part of SQL99 so likely to supported by all DBs we have. A shortcut could be introduced...

[mariuszkrzaczkowski]

Thank you very much

[dynasource]

@samdark, how do you think this shortcut should look like?

[samdark]

$c = new \yii\db\Case()->when("1>0")->then(true)->else(false);
$query->select(['c' => $c])->...

[rob006]

$query->select([
    'c' => (new \yii\db\Case())
            ->when("c > 0")->then(1)
            ->when("c < 0")->then(-1)
            ->else(0),
]);

$query->select([
    'c' => (new \yii\db\Case('c'))
            ->when("a")->then(1)
            ->when("b")->then(-1)
            ->else(0),
]);

?

Also, case is reserved keyword.

[samdark]

@rob006 what's constructor argument in the 2nd argument for?

Yeah, can't use Case :( Need another name. Probably CaseQuery would do.

[rob006]

what's constructor argument in the 2nd argument for?

To cover:

SELECT CASE c WHEN 'a' THEN -1 WHEN 'b' THEN -1 ELSE 0 END ...

This probably will require some escaping...

[samdark]

Oh... It could be multi-level.

[mariuszkrzaczkowski]

I like that. Can this query be supported?

SELECT CASE WHEN lead.status IS NULL || lead.status = "" THEN "" ELSE lead.status END AS leadstatus

[samdark]

@mariuszkrzaczkowski

$case = new \yii\db\Case())
    ->when('lead.status IS NULL || lead.status = ""')->then('')
    ->else('lead.status');

$query->select([
    'leadstatus' =>$case,
]);

[alex-code]

Probably need support for params too
Either in the method or as a separate one.

$case = new \yii\db\Case()
    ->when('lead.status IS NULL || lead.status = :param', [':param' => 1234])->then('')
    ->else('lead.status');

$case = new \yii\db\Case()
    ->when('lead.status IS NULL || lead.status = :param')->then('')
    ->else('lead.status')
    ->params([':param' => 1234]);

[samdark]

Parameters are already handled on query level so I don't think there's a need to add special support at CaseQuery level.