Support case when then end expressions?

[morloderex]

Hey guys.

Could we consider adding support for case when then syntax?

The general idea being something like:

Website::select(
    new Alias(
        new Case([
            new CaseBlock(when: new Equal('url', new Value('/exam\'ple1')), then: new Value('false'))
            new CaseBlock(when: new Equal('url', new Value('testing')), then: new Value('true'))
        ], else: new Value('false')), 
    'has-url')
)->first()

Would produce something like:

select (case when `url` = "/exam\'ple1" then "false" when `url` = "testing" then "true" else "no" end) as `has-url` from websites

[tpetry]

Its on my list. But I don't have a nice idea for the API yet. And currently no time.

[kohenkatz]

I was thinking about the API for this as well. I think the hard part of the API design is that CASE has two different ways of using it:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

and

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

If we can agree on a nice API, I will see if my boss will give me time to make a Pull Request.

Here are my initial thoughts:

Case class has a variadic constructor, something like this: public function __construct(CaseClause ...$clauses)

CaseClause is an interface (or abstract class) with the following derived types:

For the first CASE format above:

• WhenCondition - outputs WHEN condition THEN result
• Else - outputs ELSE result

Example:

new Case(
    new WhenCondition(condition: new Equal(...), result: new Value(...)),
    new WhenCondition(condition: new Equal(...), result: new Value(...)),
    new Else(result: new Value(...)),
)

For the second CASE format above:

• Expression - outputs the expression that will be matched
• WhenValue - outputs WHEN value THEN result
• This also uses the same Else described above

Example:

new Case(
    new Expression('column_name'),
    new WhenValue(value: new Value(...), result: new Value(...)),
    new WhenValue(value: new Value(...), result: new Value(...)),
    new Else(result: new Value(...)),
)

The Case class will have to check the input to make sure it meets the following conditions:

• Only one Else clause
• Zero or one Expression clause
• Either using Expression and WhenValue OR using WhenCondition, but not mixing them
• At least one WhenValue provided if using Expression
• At least one WhenCondition provided if NOT using Expression

Question: will we allow building these dynamically (e.g. addClause and/or fluent when, else methods)?

[tpetry]

I would only support the first CASE format as it is a superset of the other one. The current API is problematic as you could add multiple Else classes.

Question: will we allow building these dynamically (e.g. addClause and/or fluent when, else methods)?

Only constructors. I don't want to impose a usage design yet.

[tpetry]

Switching the values of WhenConditionwould be make the constructor similar to the WhenElse constructor. Just one parameter more but same order.

[kohenkatz]

The current API is problematic as you could add multiple Else classes.

I thought about doing an else parameter (like @morloderex suggested), but varargs need to be the last parameter and I didn't like the look of putting the else first before all the other conditions, especially because the order of all the other conditions actually matters. That's why I suggested a runtime check for only one Else.

I have not (yet) tested whether it works to put a named argument after a set of variadic arguments. If that works, it can still allow for a cleaner calling syntax, despite having to define the else parameter first. Do you know if this works?

[tpetry]

I prefer passing an array instead of using varargs. The order wouldn't be a problem anymore with this.

[morloderex]

I like the idea of just making the caller pass an array of one of the classes @kohenkatz suggested.

We could do a runtime check to make sure the Expression is first and it is only present one-time. The same is true with the ElseClause to make sure there is no more of those at the end?

If we do these kind of checks we can make everything a varargs parameter, it it could also just be typed as an iterable with the generic type of CaseClause.

So something like a if we have more than 1 Expression class and at the beginning of the iterable and more than one ElseClause at the end of the array we could throw an InvalidArgumentException?

[tpetry]

The problem with runtime check is that they are runtime checks... We now have very good static type checking with PHPstan and Psalm. Why workaround that by using non-clear types again?

My idea would be:

new Case(
  when: [
    new CaseCondition(result: new Value(...), condition: new Equal(...)),
    new CaseCondition(result: new Value(...), condition: new Equal(...)),
  ],
  else: new Value(...),
)

The result and condition are inversed as it is easier to read. The condition can be much more lengthy than the result. And its clearer when reading the code to see first the result and then the condition that leads to it.

[tpetry]

This feature has been released a few months ago.