DB CASE WHEN in SELECT

[mariuszkrzaczkowski]

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

DB support for CASE WHEN

• [x] MySQL
• [x] PostgreSQL
• [ ] MSSQL
• [ ] Oracle
• [x] 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.

[alex-code]

That's true, but it would keep it self contained and reusable in more than one query, similar to how Expression is.

[rob006]

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

We could say the same about yii\db\Expression. And most of yii\db\Query methods.

[cebe]

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

CaseExpression

[samdark]

@cebe yes, a good name.

[mariuszkrzaczkowski]

Where is this file?

[samdark]

Which file?

[mariuszkrzaczkowski]

\yii\db\Case

[samdark]

In our heads. We're just thinking loud.

[mariuszkrzaczkowski]

Ok I understand good ideas. Case should work in OrderBy

[samdark]

No only in orderBy. It should be everywhere.

[mariuszkrzaczkowski]

You also have to predict many conditions

CASE 
          WHEN org.size IN (0, 1) THEN '<26'
          WHEN org.size = 2 THEN '26-50'
          WHEN org.size = 3 THEN '51-100'
          WHEN org.size = 4 THEN '101-250'
          WHEN org.size = 5 THEN '251-500'
          WHEN org.size = 6 THEN '501-1000'
          WHEN org.size = 7 THEN '1001-5000'
        ELSE '5000+' END AS Size, ....

[samdark]

$case = new \yii\db\CaseExpression())
   ->when(['org.size' => [0, 1]])->then('<26')
   ->when(['org.size' => 2])->then('26-50')
   ->when(['org.size' => 3])->then('51-100')
   ->when(['org.size' => 4])->then('101-250')
   ->when(['org.size' => 5])->then('251-500')
   ->when(['org.size' => 6])->then('501-1000')
   ->when(['org.size' => 7])->then('1001-5000')
   ->else('5000+');

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

[Vovan-VE]

There are mix of 'plain value' and 'table.column' in an examples above. Probably, then() and else() needs pair methods like thenColumn() and elseColumn().

Also, PHP < 7 does not allow method else().

[samdark]

Also, PHP < 7 does not allow method else().

orElse?

[SilverFire]

I'd prefer default

[vercotux]

Unfortunately default was also a reserved keyword in PHP<7.0. Also when() and then() should not be separate methods anyway since you would never call one without the other, would you? How about prefixing the methods with add, since that's pretty much what you're doing (adding conditions):

$case = (new CaseExpression())
    ->addWhen(['org.size' => [0,1]], '<26')
    ->addWhen(['org.size' => 2], '26-50', $optionalParams = [])
    ->addWhen(['org.size' => 3], '51-100')
    ->addElse('100+');

It would produce:

CASE
    WHEN org.size IN (0, 1) THEN '<26'
    WHEN org.size = 2 THEN '26-50'
    WHEN org.size = 3 THEN '51-100'
    ELSE '100+'
END

And for the abbreviated "simple case" syntax:

$case = (new CaseExpression('org.size'))
    ->addWhen(1, '<26')
    ->addWhen(2, '26-50')
    ->addWhen(3, '51-100')
    ->addElse('100+');

It would produce:

CASE org.size
    WHEN 1 THEN '<26'
    WHEN 2 THEN '26-50'
    WHEN 3 THEN '51-100'
    ELSE '100+'
END

@samdark I wonder how you would build those array-based conditions inside CaseExpression though? It would require access to yii\db\Connection which is not available within CaseExpression assuming it extends from yii\db\Expression.

[samdark]

@SilverFire is it possible to implement such a thing with your recent proposal?