Doctrine IF Statement-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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

    // ...