如何用Gorm在Grails中使用GROUP_CONCAT

Question

我需要在Grails中使用GROUP_CONCAT聚合函数，最好来自HQL，但也可以使用标准。

我有以下疑问：

ClickTracking.executeQuery("SELECT pageId, containerId, GROUP_CONCAT(clicks) as click" +
                                            "FROM  ClickTracking " +
                                            "WHERE pageId =  ? " +
                                            "GROUP BY containerId ", [pageId])

Configuration conf = grailsApplication.getMainContext().getBean("&sessionFactory").configuration;
conf.addSqlFunction("GROUP_CONCAT", new StandardSQLFunction("GROUP_CONCAT", new StringType()));

import org.hibernate.dialect.MySQL5InnoDBDialect
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.Hibernate

class ExtendedMySqlDialect extends MySQL5InnoDBDialect {

public ExtendedMySqlDialect() {
    super();
    registerFunction("GROUP_CONCAT", new StandardSQLFunction("GROUP_CONCAT", Hibernate.STRING));

}

dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
dialect = "ExtendedMySqlDialect"
logSql = true

}

还是没运气。我得到：

No data type for node: org.hibernate.hql.ast.tree.MethodNode 

-[METHOD_CALL]methodnode:'('+-[METHOD_NAME]identnode:'group_concat'{originaltext=group_concat}-[EXPR_LIST]sqlnode:'expr list'-[DOT]dotnode:'clicktrack0_.clicks'{propertyname=clicks,dereferencetype=all,propertyath=clicks,path={synthet-alias}.clicks,tablealias=clicktrack0_,‘{OriginalText=clicks}.Stacktrace如下：消息：node：org.hibernate.hql.ast.tree.methodnode-[METHOD_CALL]methodnode：'('+-[METHOD_NAME]identnode：'group_concat’{OriginalText=group_concat}-[EXPR_LIST]sqlnode：'expr list‘-[DOT]dotnode：'clicktrack0_.clicks’{propertyname=clicks,ent]identNode:'{合成-别名}‘{OriginalText={合成-别名}}-[IDENT]identNode:'clicks'{OriginalText=Clicks}

Line | Method

如果我设置了一个断点，并查看grailsapplication.getMainContext().getBean（“&sessionFactory”）.配置，我可以在其中找到一个名为sqlFuncions和GROUP_COCNAT的属性。

我做了一些调试，最后在以下代码中得到了SelectExpressionList.java：

public SelectExpression[] collectSelectExpressions() {
    // Get the first child to be considered.  Sub-classes may do this differently in order to skip nodes that
    // are not select expressions (e.g. DISTINCT).
    AST firstChild = getFirstSelectExpression();
    AST parent = this;
    ArrayList list = new ArrayList( parent.getNumberOfChildren() );
    for ( AST n = firstChild; n != null; n = n.getNextSibling() ) {
        if ( n instanceof SelectExpression ) {
            list.add( n );
        }
        else {
            throw new IllegalStateException( "Unexpected AST: " + n.getClass().getName() + " " + new ASTPrinter( SqlTokenTypes.class ).showAsString( n, "" ) );
        }
    }
    return ( SelectExpression[] ) list.toArray( new SelectExpression[list.size()] );
}

似乎n=n.getNextSibbling（）在某种程度上搞砸了group_concat，但这很奇怪，因为它来自antlr包。

不管怎样，我很困惑，我很好奇如何使用group_concat（或grails中任何其他特定于db的html" target="_blank">函数和gorm）。我使用的是grails 2.0.4

Answer 1

尝试如下所示：

Yourdomain.withSession{ session ->  
   session.createSQLQuery(yourQueryWithGroup_concat).setLong(yourParameterName,yourParameterValue).list()  
}