symfony2: how to use group_concat in QueryBuilder

Question

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?

Answer 1

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