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

问题描述

我正在使用嵌套（使用Gedmo树）实体称为位置。实体Appartment具有location_id，我需要做的是将标量值（例如path）映射到查询返回所有公寓。

在Doctrine1中，我有代码：

  / ** 
 *为每个元素添加路径
 * 
 * @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别名用于主查询。
此代码将允许我使用

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

哪个打印：

 澳大利亚，维多利亚州，墨尔本，...其他孩子... 
  

如何在D2中做同样的事情？如何在我的symfony2项目中包含教义扩展？

解决方案

如果要在QueryBuilder中使用它，您必须： p>

1）添加DQL函数GroupConcat： GroupConcat

2）注册GroupConcat： DQL用户定义的函数

<另一个选择是使用NativeQuery： Native SQL

---

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

  entity_managers：
默认值：
 dql：
 string_functions：
 GROUP_CONCAT：YourBundle\Query\Mysql\GroupConcat 
  

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

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?

解决方案

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

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