Home Symphony Full GROUP_CONCAT support in Doctrine2

Full GROUP_CONCAT support in Doctrine2

by admin

Hi all.
As it happens, the project I’m working on right now required the GROUP_CONCAT() function.Unfortunately, Doctrine2 doesn’t support this function out of the box. The existing extension extension from one of Doctrine2 developers (Benjamin Eberlei) is listed as "limited support for GROUP_CONCAT".I understand that using this function makes the project automatically dependent on MySQL, but we’re not planning on changing the database system like gloves. So leave this question out of the post.
Since I didn’t find any solution by googling, I decided to write one myself (using Benjamin’s development as a base). There is not much to comment there, so I just presented 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 DoctrineExtensionsQueryMysql;use DoctrineORMQueryASTFunctionsFunctionNode, DoctrineORMQueryLexer;/*** 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(DoctrineORMQueryParser $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(DoctrineORMQuerySqlWalker $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;}}

Example of use :

$query = $this-> createQueryBuilder('c')-> select("c as company, GroupConcat(b.id, ';', b.headOffice, ';', b.city, ';', s.nameORDER by b.idSEPARATOR '|') 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 :
Registration of custom DQL function in Doctrine2
How to plug in custom DQL functions in Symfony2
Description of the MySQL function GROUP_CONCAT

You may also like