symfony - 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: YourBundleQueryMysqlGroupConcat

For more information visit Registering Custom DQL Functions