symfony2: comment utiliser group_concat dans QueryBuilder

Si vous voulez l'utiliser dans le QueryBuilder, vous devez :

1) ajouter DQL fonctions GroupConcat: GroupConcat

2 ) Enregistrement GroupConcat :DQL Fonctions Définies par l'Utilisateur

une autre alternative est d'utiliser NativeQuery :Natif SQL

Dans symfony2 l'enregistrement d'une fonction DQL c'est très simple il suffit d'ajouter GROUP_CONCAT dans la configuration.yml comme:

    entity_managers:
        default:
            dql:
                string_functions:
                    GROUP_CONCAT: YourBundle\Query\Mysql\GroupConcat

Pour plus d'informations, visitez Enregistrement Personnalisé DQL Fonctions

Simplement un ajout à @une.aitboudad réponse:
Le DQL fonction GroupConcat lié semble avoir une «prise en charge limitée pour GROUP_CONCAT».

Ici, c'est la pleine version de soutien :
La configuration est comme il l'a mentionné.

//-------------------------------------------------
//Complete support of GROUP_CONCAT in Doctrine2
//-------------------------------------------------
//Original Article: http://habrahabr.ru/post/181666/
//Automated translation to English: http://sysmagazine.com/posts/181666/
//Original github commit: https://github.com/denisvmedia/DoctrineExtensions/blob/d1caf21cd7c71cc557e60c26e9bf25323a194dd1/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php
/**
* 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 [email protected] 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;
}
}
//-------------------------------------------------
//Example of usage:
//-------------------------------------------------
$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();