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

Question

I have a table for my categories, each category have and id, name and 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

    $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.

Answer 1

Ok I found the solution.

You can use CASE instead of IF. Check this out, but when I am using CASE I can't CONCAT my fields:

$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 IF and use it like this:

$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

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):

<?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):

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

Answer 2

You can also combine CASE and 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)     ... ;