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

+1

That would be great for me too!
+1

+1

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

+1

+1024

@vorbeer with Phalcon\Db\RawValue, maybe?

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

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

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

@phalcon +1 :)

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

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

@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

@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

?

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

This is implemented in 2.0.3

@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

@andresgutierrez

It still does not work in postgreSql

@broklyngagah If so create issue