doctrine2 ifnull

Question

嗨，doctrine2中有ifnull吗？我需要用..。怎么做？

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

如何将这个sql转换成理论？

    $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();

找到了！！感谢@AdrienBrault的"coalesce“运算符

$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();

Answer 1

如果要在理论查询生成器中使用sql的IFNULL函数，则需要对IFNULL使用理论扩展。

首先，您需要注册扩展类，以便在Doctrine引导程序中使用

        $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();

这里是IFNULL理论扩展代码。

<?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).')';
    }
}

您可以从这里下载扩展。

对于你的案子，用这个。

$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();