将 MySQL 转换为 Doctrine 查询生成器。IF 和 CONCAT 的问题。或另一种选择子查询的方法

Converting MySQL to Doctrine Query Builder. Issues with IF and CONCAT. Or another approach for subqueries on select

提问by Thiago Festa

I have a table for my categories, each category have and id, name and parent_id.

我有一个用于分类的表格，每个类别都有 id、name 和 parent_id。

Select IF(a.parent_id IS NULL, a.name, CONCAT((SELECT b.name FROM category b WHERE b.id = a.parent_id), " / ", a.name) ) as n, a.id, a.parent_id
FROM category a
ORDER BY n

I want to convert it to my Doctrine2 Query Builder

我想将其转换为我的 Doctrine2 查询生成器

    $em = $this->getDoctrine()->getEntityManager();
    $qb = $em->createQueryBuilder();
    $q = $qb
            ->select("c.id")
            ->addSelect(
                "IF(c.parent_id IS NULL, c.name, CONCAT((" .
                $em->createQueryBuilder()
                   ->select("t.name")
                   ->from("MyBundle:Category", "t")
                   ->getQuery()->getDQL() .
                "), \" / \", c.name) )"
            )
            ->from("MyBundle:Category", "c");
    echo $q->getQuery()->getSQL();
    exit;

Something like that, but I cant use the IF, and CONCAT.

类似的东西，但我不能使用IF, 和CONCAT。

回答by Thiago Festa

Ok I found the solution.

好的，我找到了解决方案。

You can use CASEinstead of IF. Check this out, but when I am using CASEI can't CONCATmy fields:

您可以使用CASE代替IF. 看看这个，但是当我使用时CASE我不能CONCAT我的字段：

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder();
$q = $qb
      ->select("c.id")
      ->addSelect("CASE WHEN (c.parent IS NULL) THEN c.name ELSE 'something' END")
      ->from("MyBundle:Category", "c")
      ->leftJoin("c.parent", "t");

echo $q->getQuery()->getSQL();

Another Solution is create your own DQL function, like IFand use it like this:

另一个解决方案是创建您自己的 DQL 函数，像这样IF使用它：

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder();
$q = $qb
      ->select("c.id")
      ->addSelect("IF(c.parent IS NULL, c.name, CONCAT(CONCAT(t.name, ' / '), c.name))")
      ->from("MyBundle:Category", "c")
      ->leftJoin("c.parent", "t");

echo $q->getQuery()->getSQL();

For create this IF you can go to this link and learn: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language

要创建此 IF，您可以转到此链接并了解：http: //docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#adding-your -自己的函数到 dql 语言

I will post here my class for this IF and the config.yml to help other people. Here is the IfFunction class (I got that from https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/IfElse.php):

我将在这里发布我的课程和 config.yml 以帮助其他人。这是 IfFunction 类（我从https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/IfElse.php得到）：

<?php
namespace MyName\MiscBundle\Doctrine\ORM\Query\AST\Functions;

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

/**
 * Usage: IF(expr1, expr2, expr3)
 * 
 * If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2;
 * otherwise it returns expr3. IF() returns a numeric or string value,
 * depending on the context in which it is used. 
 * 
 * @author  Andrew Mackrodt <[email protected]>
 * @version 2011.06.19
 */
class IfFunction extends FunctionNode
{
    private $expr = array();

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->expr[] = $parser->ConditionalExpression();

        for ($i = 0; $i < 2; $i++)
        {
            $parser->match(Lexer::T_COMMA);
            $this->expr[] = $parser->ArithmeticExpression();
        }

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return sprintf('IF(%s, %s, %s)',
            $sqlWalker->walkConditionalExpression($this->expr[0]),
            $sqlWalker->walkArithmeticPrimary($this->expr[1]),
            $sqlWalker->walkArithmeticPrimary($this->expr[2]));
    }
}

After that you need to update your config.yml like this (just added the last 3 lines):

之后，您需要像这样更新您的 config.yml（仅添加最后 3 行）：

doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        dql: #ADDED THIS LINE
            string_functions: #ADDED THIS LINE
                IF: MyName\MiscBundle\Doctrine\ORM\Query\AST\Functions\IfFunction #ADDED THIS LINE

Thanks

谢谢

回答by justb3a

You can also combine CASEand CONCAT:

您还可以组合CASE和CONCAT：

$q = $this->createQueryBuilder('a')
    ->select('a.id')
    ->addSelect('CASE WHEN(a.parent IS NULL) THEN \'\' else CONCAT(:variable, a.name, \'string\') END as name')
    ->setParameter('variable', $variable)
    ... ;

回答by greg0ire

Assuming you have a relation named "parent" in Category, does this work better? :

假设您在 Category 中有一个名为“parent”的关系，这会更好吗？：

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder();
$q  = $qb
      ->select("c.id")
   ->addSelect("IF (c.parent_id IS NULL, c.name, CONCAT(t.name, ' / ', c.name))"
        ->from("MyBundle:Category", "c")
    ->leftJoin("c.parent t"));

echo $q->getQuery()->getSQL();