Full support for GROUP_CONCAT in Doctrine2

Hello to all.

It so happened that in the project I'm working on, it was necessary to use the GROUP_CONCAT () function. Unfortunately, Doctrine2 out of the box does not support this feature. The existing extension from one of the Doctrine2 developers (Benjamin Eberlei) is listed as "limited support for GROUP_CONCAT". I understand that using this function automatically makes the project dependent on MySQL, but it is not planned to change the DBMS as gloves. So let's leave this question outside of the post.

Since I did not find a ready-made solution, I decided to write it myself (taking Benjamin’s development as a basis). There is nothing special to comment there, so I simply submit it to the public:

/** * DoctrineExtensions Mysql Function Pack * * LICENSE * * This source file is subject to the new BSD license that is bundled * with this package in the file LICENSE.txt. * If you did not receive a copy of the license and are unable to * obtain it through the world-wide-web, please send an email * to kontakt@beberlei.de so I can send you a copy immediately. */ namespace DoctrineExtensions\Query\Mysql; use Doctrine\ORM\Query\AST\Functions\FunctionNode, Doctrine\ORM\Query\Lexer; /** * Full support for: * * GROUP_CONCAT([DISTINCT] expr [,expr ...] * [ORDER BY {unsigned_integer | col_name | expr} * [ASC | DESC] [,col_name ...]] * [SEPARATOR str_val]) * */ class GroupConcat extends FunctionNode { public $isDistinct = false; public $pathExp = null; public $separator = null; public $orderBy = null; public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $lexer = $parser->getLexer(); if ($lexer->isNextToken(Lexer::T_DISTINCT)) { $parser->match(Lexer::T_DISTINCT); $this->isDistinct = true; } // first Path Expression is mandatory $this->pathExp = array(); $this->pathExp[] = $parser->SingleValuedPathExpression(); while ($lexer->isNextToken(Lexer::T_COMMA)) { $parser->match(Lexer::T_COMMA); $this->pathExp[] = $parser->StringPrimary(); } if ($lexer->isNextToken(Lexer::T_ORDER)) { $this->orderBy = $parser->OrderByClause(); } if ($lexer->isNextToken(Lexer::T_IDENTIFIER)) { if (strtolower($lexer->lookahead['value']) !== 'separator') { $parser->syntaxError('separator'); } $parser->match(Lexer::T_IDENTIFIER); $this->separator = $parser->StringPrimary(); } $parser->match(Lexer::T_CLOSE_PARENTHESIS); } public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { $result = 'GROUP_CONCAT(' . ($this->isDistinct ? 'DISTINCT ' : ''); $fields = array(); foreach ($this->pathExp as $pathExp) { $fields[] = $pathExp->dispatch($sqlWalker); } $result .= sprintf('%s', implode(', ', $fields)); if ($this->orderBy) { $result .= ' '.$sqlWalker->walkOrderByClause($this->orderBy); } if ($this->separator) { $result .= ' SEPARATOR '.$sqlWalker->walkStringPrimary($this->separator); } $result .= ')'; return $result; } } 

')
Usage example:

 $query = $this->createQueryBuilder('c') ->select(" c as company, GroupConcat(b.id, ';', b.headOffice, ';', b.city, ';', s.name ORDER by b.id SEPARATOR '|') AS branches ")->leftJoin('c.branches','b') ->leftJoin('b.country','s') ->groupBy('c.id') ->setFirstResult(0) ->setMaxResults(10) ->getQuery() ; $result = $query->getResult(); 

Official documentation on the topic:
Register a custom DQL function in Doctrine2 .
How to plug into DQL user functions in symfony2 .
Description of the MySQL function GROUP_CONCAT .