[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. 💩

[phalcon]

@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?

[wandersonwhcr]

@tmihalik in postgresql I created a function called "if" with 3 parameters 💩

[phalcon]

@tmihalik maybe you want to extend the dialect to support that function:

• https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Db#dialectmysqlextended
• https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php

[tmihalik]

@phalcon Thanks! Extending the getSqlExpression method of the dialect class I was able to create a new functionCall type expression named "expr()" to handle any custom statements I want. Actually it's just returns the first argument to the query as it is.

// postgreSQL
$this->modelsManager->createBuilder()->columns(['col1, col2',
    "expr('CASE WHEN TRUE THEN 1 ELSE 0 END') AS raw",
    "expr('name || age') AS name_and_age"
])->from(...

So this is a workaround, however I still think there should be some easy built-in - documented - way to handle this common problem. A class like Phalcon\Db\RawValue working really as easy as "hey, put that string to the query as it is, I now what I'm doing". It could be Phalcon\Db\RawExpression

?

[wandersonwhcr]

@tmihalik Phalcon uses a high layer to build SQL called Phalcon\Db. It outputs an SQL language called PHQL (Phalcon Query Language), interpreted and parsed internally, to the target database.

I think we can't check if some code is a RAW value at "parse time". I think we need to change the parser to understand the new expression.

[andresgutierrez]

This is implemented in 2.0.3

[tmihalik]

@andresgutierrez

Great news!
Thank you for implementing this.
However Searched CASE syntax still throws syntax error. (first post)

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

[broklyngagah]

@andresgutierrez

It still does not work in postgreSql

[sergeyklay]

@broklyngagah If so create issue