GORM / HQL - LISTAGG

Question

in GORM (using grails) i need to combine in subselect multiple results into one value. (result of this subselect will be concatenated value on which i can make search / sort etc. ...) Query is writen as HQL. Something like this in oracle http://www.techonthenet.com/oracle/functions/listagg.php

Can be something like that achieved in HQL (e.g. GORM) ?

...
AND (
    SELECT LISTAG(e.title) AS con FROM Entity e
    WHERE Entity.searchKey = TRUE
    AND e.parrent = par
    AND LOWER(e.title) LIKE :search
) > 0
...
ORDER BY con ASC

thansk

Answer 1

Hibernate, and the HQL/GORM layers that sit on top of Hibernate, do not directly support database-specific functions like Oracle's LISTAGG(). There are, however, a few ways to use native SQL within Grails. If you would like to add your concatenated value to one of your domain objects, you can use GORM's derived property feature (http://grails.org/doc/latest/guide/GORM.html#derivedProperties).

Something along these lines:

class MyDomain {
    Long parentId
    String titleAgg

    static mapping = {
        titleAgg formula: '(SELECT LISTAGG(e.title) FROM Entity e WHERE e.parrent = parent_id)'
    }

}

A second option would be to use the Grails-defined dataSource bean along with groovy.sql.Sql to execute native SQL statements. See here for an example.