symfony2: how to use group_concat in QueryBuilder
doctrine-orm, symfony
Solution
If you want to use it in QueryBuilder you must :
1) add DQL functions GroupConcat: GroupConcat
2 ) Registering GroupConcat :DQL User Defined Functions
another alternative is to use NativeQuery :Native SQL
In symfony2 registering a function DQL it's very simple just add GROUP_CONCAT in config.yml like:
entity_managers:
default:
dql:
string_functions:
GROUP_CONCAT: YourBundle\Query\Mysql\GroupConcat
For more information visit Registering Custom DQL Functions
Problem
I am having nested-set (using Gedmo tree) entity called "Location". Entity "Appartment" has location_id and what I need to do it to map scalar value called eg "path" to query that returns all appartments. In Doctrine1, I had this code: ``` /** * Add "path" to each element * * @param Doctrine_Query $query * @param string $separator */ protected function addScalar_path(Doctrine_Query $query, $separator=", ") { $subquery = "k99.root_id=o.root_id AND k99.lft<=o.lft AND k99.rgt>=o.rgt AND k99.level<=o.level" ; $query->addSelect("(SELECT GROUP_CONCAT(k99.name ORDER BY k99.level SEPARATOR '$separator') FROM Location k99 WHERE $subquery) AS path") ; } ``` Note: "o" alias is used for primary query. This code would allow me to use ``` {foreach .... as $appartment} {$appartment->path} ... ``` Which would print: ``` Australia, Victoria, Melbourne, ...other children... ``` How to do the same thing in D2? And how to even include doctrine extenstions in my symfony2 project?