symfony2:如何在 QueryBuilder 中使用 group_concat [英] symfony2: how to use group_concat in QueryBuilder

问题描述

我有一个名为位置"的嵌套集(使用 Gedmo 树)实体.实体Appartment"具有 location_id 以及我需要做什么来将名为path"的标量值映射到返回所有公寓的查询.

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.

在 Doctrine1 中，我有这样的代码:

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") ;
}

注意:o"别名用于主查询.此代码将允许我使用

Note: "o" alias is used for primary query. This code would allow me to use

{foreach .... as $appartment}
   {$appartment->path}
...

哪个会打印:

Australia, Victoria, Melbourne, ...other children...

如何在 D2 中做同样的事情?以及如何在我的 symfony2 项目中甚至包含学说扩展?

How to do the same thing in D2? And how to even include doctrine extenstions in my symfony2 project?

推荐答案

如果你想在 QueryBuilder 中使用它，你必须:

If you want to use it in QueryBuilder you must :

1) 添加 DQL 函数 GroupConcat:GroupConcat

1) add DQL functions GroupConcat: GroupConcat

2) 注册 GroupConcat :DQL 用户定义函数

2 ) Registering GroupConcat :DQL User Defined Functions

另一种选择是使用 NativeQuery :本机 SQL

another alternative is to use NativeQuery :Native SQL

在 symfony2 中注册一个 DQL 函数非常简单，只需在 config.yml 中添加 GROUP_CONCAT 就像:

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

有关更多信息，请访问注册自定义 DQL 函数

这篇关于symfony2:如何在 QueryBuilder 中使用 group_concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！