Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

PHQL Syntax error, unexpected token IDENTIFIER(when)

I was trying make this query by PHQL:

$query = $this->modelsManager->createQuery("select team, sum(case when used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team");
$Genkeys = $query->execute();
    foreach ($Genkeys as $genkey) {
        $this->view->disable();
        var_dump($genkey);
    }

Whe I tryed that in phpmyadmin as raw sql it was working fine but when I run it in controller I see this error:

Syntax error, unexpected token IDENTIFIER(when), near to ' used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team', when parsing: select team, sum(case when used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team (143)


17.3k

Try (used = 0 and team = 2) or set an ELSE in the statement.



2.5k
edited Oct '14

Try (used = 0 and team = 2) or set an ELSE in the statement.

But then it will not sum points I need to sum points but only of that rows where used is 0 and only of specific team.



81.8k
Accepted
answer
edited Oct '14

This syntax: case when used = 0 and team = 2 then points end is not supported by PHQL, you can use an IF:

IF(used = 0 and team = 2, points, 0)



2.5k


1.8k

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?

Why is this marked as Solved when it still doesn't work in PostgreSQL?

edited May '16

I'am using 2.0.8 and this valid Postgresql statement is still not working:

CASE WHEN filesize IS NOT NULL THEN filesize[0] || 'x' || filesize[1] ELSE NULL END AS filesize

leads to the following Error:

Syntax error, unexpected token WHEN, near to ' file_size

The problem is not the CASE, it's that you're trying to concatenate using || which is a postgresql-only operator, you can:

edited May '16

Same error on:

CASE WHEN filesize IS NOT NULL THEN filesize ELSE NULL END AS file_size

:(

The problem is not the CASE, it's that you're trying to concatenate using || which is a postgresql-only operator, you can: