[NFR] PHQL: CASE expressions

[igorgolovanov]

I think, we needs to provide a new built-in functionality into PHQL for working with CASE syntax.
There're two syntaxes: Simple CASE and Searched CASE.

---

Simple CASE syntax

The simple CASE function compares an expression to a set of simple expressions to determine the result.

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
        statements
    [ WHEN expression [, expression [ ... ]] THEN
        statements
    ... ]
    [ ELSE
        statements ]
END

The simple form of CASE provides conditional execution based on equality of operands.
The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses.
If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END CASE.
If no match is found, the ELSE statements are executed; but if ELSE is not present, then must be returned a NULL value.

---

Searched CASE syntax

The searched CASE function evaluates a set of Boolean expressions to determine the result.

CASE
    WHEN boolean-expression THEN
        statements
    [ WHEN boolean-expression THEN
        statements
    ... ]
    [ ELSE
        statements ]
END

The searched form of CASE provides conditional execution based on truth of boolean expressions.
Each WHEN clause's boolean-expression is evaluated in turn, until one is found that yields true.
Then the corresponding statements are executed, and then control passes to the next statement after END CASE.
If no true result is found, the ELSE statements are executed; but if ELSE is not present, then must be returned a NULL value.

---

General Agreement

• if ELSE is not present, then must be returned a NULL value. Because different db have a different behavior in this case, i think that PHQL must always add "ELSE NULL" if else not presented in user query.
• CASE must be closed with END.

---

Reference Manuals:

• MySql: http://dev.mysql.com/doc/refman/5.0/en/case.html
• Postgresql: http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS
• Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm
• Microsoft Sql Server: http://msdn.microsoft.com/en-us/library/ms173318.aspx
• Sqlite: http://www.sqlite.org/lang_expr.html

---

Where it implemented?

• Doctrine: http://docs.doctrine-project.org/de/latest/reference/dql-doctrine-query-language.html#case-expressions
• Hibernate: http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch11.html#d5e3099

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[brzuchal]

That would be great I was just looking for whis. Would it be ever implemented ?

[joeyew]

+1

[joebak]

That would be great for me too!
+1

[wandersonwhcr]

+1

[vorbeer]

+1
Is there any way to force this statement not be parsed and passed into SQL as is?

[ddenysov]

+1

[phoenixgao]

+1024

[wandersonwhcr]

@vorbeer with Phalcon\Db\RawValue, maybe?

http://docs.phalconphp.com/en/latest/api/Phalcon_Db_RawValue.html

[tmihalik]

+1

@wandersonwhcr As reading the docs it seems to me that Phalcon\Db\RawValue stands exactly for situations like this but it throws Phalcon\Mvc\Model\Exception when I try to execute a query with CASE WHEN statement. :(

$raw = new \Phalcon\Db\RawValue('CASE WHEN TRUE THEN 1 ELSE 0 END AS raw');

$query = $this->modelsManager->createBuilder()->columns(['col1, col2', $raw])
->from(...

$sql = $query->getPhql();  //debug
$query->getQuery()->execute(); //this line throws the exception

Debugging $sql shows a well formatted query, there's no problem here.
I can't understand why. Is it a bug?

[wandersonwhcr]

hi @tmihalik 😄

It's not a bug, I think.

After 4 months learning Phalcon, I think it is a new feature to implement in PHQL parser, which don't recognize CASE statements inside column definitions. If I remember, PHQL recognize CASE as a function call, waiting to an open bracket in the next token. 💩

@tmihalik you can use IF:

SELECT IF(TRUE,1, 0) AS raw FROM Store\Robots

[wandersonwhcr]

@phalcon +1 :)

[tmihalik]

@phalcon It works for Mysql, but not with Postgresql.

This problem is nicely solved in Kohana framework and it's very useful.

http://kohanaframework.org/3.3/guide/database/query/builder#database-expressions

Something like this would be great in Phalcon. What do you think?