Storing "CASE WHEN" condition in Doctrine2 entity

18
votes

As of Doctrine 2.1, the CASE WHEN statement is supported but there isn't a lot of documentation on it. My goal is to set a boolean value to tell if a photo has been favorited by a user:

       ->addSelect("CASE WHEN f.photo is NULL THEN false ELSE true END as is_favorited")
       ->leftJoin("p.favorites", 'f', 'WITH', 'f.owner = :viewer')
       ->orderBy("p.date_posted", "DESC")
       ->setParameters(array("owner" => $owner, "viewer" => $viewer));

But because my entities are being transformed into json by JMSSerializer, I would like to set the CASE WHEN result as a property on the entity.

        ->addSelect("CASE WHEN f.photo is NULL THEN false ELSE true END as p.is_favorited")

But unforunately Doctrine does not seem to like this:

[Syntax Error] line 0, col 65: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '.'

Is there an alternative to setting DQL created properties on an entity?

4
phpmysqlsymfonydoctrine-orm
Does the result set mapping work with DQL? - gremo
Would ->addSelect('f.photo IS NOT NULL') not have the same effect (but perhaps not trip up Doctrine)? - Tomas Creemers
Did you try simply encapsulating the CASE in () like (CASE WHEN f.photo is NULL THEN false ELSE true END) as is_favorited? - prodigitalson

4 Answers

2
votes

There is no way to set property value in query directly. Instead, you can add not-mapped field into entity, and then walk through results and set it. In this case JMSSerializer serialize that field and you can set necessary type and other meta information on it.

0
votes

Can you try this. it will work with DQL

->addSelect("SUM(CASE WHEN f.photo is NULL THEN 0 ELSE 1 END) as p.is_favorited");
-1
votes

From the error it sounds like it's complaining because you don't have a FROM table listed in your select statement.

-1
votes

You just need to add your 

->from()

line that gives the table name for "F" and