symfony - doctrine2 ifnull

Question

嗨，在 doctrine2 中有 ifnull 吗？我需要使用...如何使用？

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

如何把这个sql转成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();

---

找到了!!!感谢@AdrienBrault 的“合并”运算符

$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

如果你想在doctrine query builder中使用sql的IFNULL函数，你需要使用doctrine extension for IFNULL。

首先你需要注册扩展类以在 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();

这里是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).')';
    }
}

您可以从 here 下载扩展

对于你的情况，使用这个。

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