Using comparison as an alias in the choice for Doctrine2

Question

Trying to do this in Doctrine2:

...->createQuery('SELECT m.id, (m.status != 1) as verified... 

But this causes an error - if I output the brackets, I get another error. How to achieve this m.status comparison?

thanks

Answer 1

Doctrine 2 does not support these comparisons in the SELECT clause (at least not up to 2.3, not sure about 2.4).

You can use the CASE expression as a workaround:

 SELECT m.id, CASE WHEN m.status != 1 THEN 1 ELSE 0 END AS verified ... 

or

 SELECT m.id, CASE WHEN m.status = 1 THEN 0 ELSE 1 END AS verified ... 

If you need verified for an ORDER BY clause (or something like that), but it really is not needed as a result, you can use the HIDDEN expression:

 SELECT m.id, CASE WHEN m.status = 1 THEN 0 ELSE 1 END AS HIDDEN verified ... 

A completely different solution is to write a custom DQL function .

Answer 2

You can use the solution proposed here: Cumulative DQL with a doctrine

When working with entities, keep in mind that adding selections will cause the query to return an array for each result:

 $res = $em->createQueryBuilder() ->from('BlogPost', 'p') ->select('p') ->addSelect('(2+3) AS dummy') ->getQuery()->getResult(); 

Iterating over $ res will return an array:

 foreach($res as $mixed){ echo get_class($mixed[0]); //$mixed[0] contains the BlogPost echo $mixed['dummy']; //displays the dummy result (5) } 

Answer 3

check this: 13.2.4. Using Expr * Classes to Create Conditional Expressions

Using expression methods, you can do something like:

 $qb = $this->entityManager->createQueryBuilder(); $query = $qb->select('m.id') ->from('Entities\MyEntity', 'm') /*neq() is the "not equal" comparison function*/ ->where($qb->expr()->neq('m.status', $someValue)), ->getQuery();