How to use GROUP_CONCAT in Grails with Gorm

How to use GROUP_CONCAT in Grails with Gorm

I need to use GROUP_CONCAT aggregate function in Grails, preferably from
HQL, but also with criteria is ok.
I have this query:
ClickTracking.executeQuery("SELECT pageId, containerId,
GROUP_CONCAT(clicks) as click" +
"FROM ClickTracking " +
"WHERE pageId = ? " +
"GROUP BY containerId ",
[pageId])
This is not working because HQL is not knowing about GROUP_CONCAT because
is DB specific. I am ok to tie my project with MySQL, so I tried to add
this in BootStrap.groovy:
Configuration conf =
grailsApplication.getMainContext().getBean("&sessionFactory").configuration;
conf.addSqlFunction("GROUP_CONCAT", new
StandardSQLFunction("GROUP_CONCAT", new StringType()));
No luck.
Then I tried to subclass the dialect and use it:
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));
}
}
and in DataSource.groovy
dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
dialect = "ExtendedMySqlDialect"
logSql = true
}
Still no luck. I get:
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: 'exprList' -[DOT]
DotNode: 'clicktrack0_.clicks'
{propertyName=clicks,dereferenceType=ALL,propertyPath=clicks,path={synthetic-alias}.clicks,tableAlias=clicktrack0_,className=com.nokia.primeplace.ui.gorm.ClickTracking,classAlias=null}
+-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
-[IDENT] IdentNode: 'clicks' {originalText=clicks} . Stacktrace follows:
Message: 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: 'exprList' -[DOT]
DotNode: 'clicktrack0_.clicks'
{propertyName=clicks,dereferenceType=ALL,propertyPath=clicks,path={synthetic-alias}.clicks,tableAlias=clicktrack0_,className=com.nokia.primeplace.ui.gorm.ClickTracking,classAlias=null}
+-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
-[IDENT] IdentNode: 'clicks' {originalText=clicks}
Line | Method
->> 156 | initializeExplicitSelectClause in
org.hibernate.hql.ast.tree.SelectClause
If I set a breakpoint and I look in
grailsApplication.getMainContext().getBean("&sessionFactory").configuration
I can find there a property named sqlFuncions and GROUP_COCNAT is there.
I did some debugging and I ended up in SelectExpressionList.java in this
code:
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()] );
}
Seems that n=n.getNextSibbling() is somehow messing up with group_concat,
but this is strange because that is from antlr package.
Anyway, I am stuck and I am curious how can I use group_concat (or any
other db specific functions in grails with gorm). I am using grails 2.0.4