doctrine2 ifnull

Question

hi is there in doctrine2 ifnull? i need to use... how to?

SELECT *
FROM `apns_task_message`
ORDER BY IFNULL( `sent_at` , NOW( ) ) , `priority` DESC , `position` ASC

how convert this sql into doctrine?

    $qb = $this->getRepository()->createQueryBuilder('tm');
    $qb->leftJoin('tm.apnsTask', 't');
    $qb->add('where', 't.id = :task_id')->setParameter('task_id', $task_id);
    //$qb->add('orderBy', 'IFNULL(tm.sent_at, NOW()), tm.priority DESC, tm.position ASC');
    $qb->add('orderBy', 'd_date, tm.priority DESC, tm.position ASC');
    $q = $qb->getQuery();
    return $q->getResult();

found!!! Thanks to @AdrienBrault for "coalesce" operator

$now = new \DateTime("now");
$qb = $this->getRepository()->createQueryBuilder('tm');
$qb->addSelect('coalesce (tm.sentAt, :sent_date) as sent_date')->setParameter('sent_date', $now->format("Y-m-d H:i:s"));
$qb->leftJoin('tm.apnsTask', 't');
$qb->add('where', 't.id = :task_id')->setParameter('task_id', $task_id);
$qb->add('orderBy', 'sent_date ASC, tm.priority DESC, tm.position ASC');
$q = $qb->getQuery();

[Syntax Error] line 0, col 31: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '(' — Sergey Gerdel, Jul 22 '12 at 13:41
doesn't work/ [Syntax Error] line 0, col 44: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '(' — Sergey Gerdel, Jul 22 '12 at 14:15
I know this is really old, but was this ever resolved? I can't seem to CONCAT/COALESCE without getting the "...\Lexer::T_CLOSE... got '('" type error. The only progress I've made is achieving different errors, haha. — Steve Adams, Oct 27 '12 at 20:48
You should Answer your question and accept it. Don't leave it in the question section. — stevenll, Apr 06 '15 at 16:59

Virendra Jadeja · Answer 1 · 2017-06-09T04:59:48.930

If you want to use IFNULL function of sql in doctrine query builder, You need to use doctrine extension for IFNULL.

First of all you need to register extension class to use in Doctrine bootstrap

        $config = Setup::createAnnotationMetadataConfiguration($metadata_paths, $dev_mode, $proxies_dir);
        $driver = new AnnotationDriver(new AnnotationReader());
        $config->setMetadataDriverImpl($driver);

        require_once './Doctrine/DoctrineExtensions/Query/MySql/IfNull.php';

        $config->addCustomStringFunction('IfNull', '\DoctrineExtensions\Query\Mysql\IfNull');

        $this->em = EntityManager::create($connection_options, $config);

        $classLoader = new ClassLoader('DoctrineExtensions', $extension_dir);
        $classLoader->register();

Here are IFNULL doctrine extension code.

<?php

namespace DoctrineExtensions\Query\Mysql;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

/**
 * @author Andrew Mackrodt <andrew@ajmm.org>
 */
class IfNull extends FunctionNode
{
    private $expr1;
    private $expr2;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->expr1 = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_COMMA);
        $this->expr2 = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'IFNULL('
            .$sqlWalker->walkArithmeticPrimary($this->expr1). ', '
            .$sqlWalker->walkArithmeticPrimary($this->expr2).')';
    }
}

You can download extensions from here

For your case, use this.

$qb = $this->getRepository()->createQueryBuilder('tm');
return $qb
        ->select("*")
        ->from("tm.apnsTask", "t")
        ->orderBy("IFNULL(t.sent_at, NOW()), t.priority DESC, t.position ASC")
        ->getQuery()
        ->getResult();

doctrine2 ifnull

1 Answers1