Doctrine IF Statement

Question

I have the following code in Doctrine:

return $this->createQueryBuilder('t') ->where('t.status LIKE :status') ->orderBy('t.createdAt', 'DESC') ->setParameter('status', $status) ->setMaxResults($limit) ->getQuery() ->getResult() ;

However the real SQL query I want to build is this:

SELECT t.*, IF(deleted = 1, 4, status) AS status FROM trm t WHERE status = 3 ORDER BY created_at DESC;

Essentially replacing the status column with one that = 4 if the column 'deleted' is true.

How can I recreate this in the doctrine query above?

I have tried looking into ->expr()->eq but I can't find any easy examples to follow. Im not even sure if they are what I need to use.

Will B. · Accepted Answer · 2022-04-04 01:20:53Z

Doctrine does not have IF expression support by default. Instead CASE expressions are supported, which can be converted to: CASE t.deleted WHEN 1 THEN 4 ELSE t.status END

Your original query converted to the ORM QueryBuilder would be:

$this->createQueryBuilder('t') ->addSelect('CASE t.deleted WHEN 1 THEN 4 ELSE t.status END AS status') ->where('t.status LIKE :status') ->orderBy('t.createdAt', 'DESC') ->setParameter('status', $status) ->setMaxResults($limit) ->getQuery() ->getResult();

However, this will not modify the value sent to the entity but instead produce a nested array result with the status value separated.

array array [0] => Entity (Object) ['status'] => 4 array [0] => Entity (Object) ['status'] => 4 // ...

Thank you, this is about as close as I'm going to get I think :)

Rufinus · Accepted Answer · 2022-04-01 18:04:07Z

you would need DoctrineExtnesions ( https://github.com/beberlei/DoctrineExtensions )

orm: .. entity_managers: .... dql: .... string_functions: IF: DoctrineExtensions\Query\Mysql\IfElse

but modifying the entity in such way is not a good practice.

Collectives™ on Stack Overflow

Doctrine IF Statement

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related