Grails queries with criteria: how to get back a map with column?

Question

Is it possible, to query grails with criteria and receive a list of maps instead of a list of lists? I would like to have the column names in the results in order to then work with an 'associative array' rather then numeric array offsets. I currently do something like

    def topFiveUsers = BlogEntry.createCriteria().list {
        projections {
            count('id')
            groupProperty('author')
        }
        maxResults 5
    }

Which results in [[123, app.User:1][111, app.User:2][...]...], i.e. a list of lists. I would rather want something like [[posts:123, author: app.User:1][posts: 111, author app.User:2][...]...].

As always: help is highly appreciated!

score 31 · Answer 1 · edited Jan 12 '16 at 09:06

31

Use resultTransformer(). As the parameter use CriteriaSpecification.ALIAS_TO_ENTITY_MAP
The documentation and examples on this topic is scarce. But here's an example:

import org.hibernate.criterion.CriteriaSpecification

BlogEntry.withCriteria {
  maxResults 5
  resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
  projections {
    count('id', 'total')
    groupProperty('author', 'author')
  }      
}

Note that alias is required for all projections. Otherwise, the resulting map consists of nulls.

edited Jan 12 '16 at 09:06

Shashank Agrawal

25,161
11
89
121

answered May 07 '13 at 00:50

Diamond Hands

803
1
10
16

1

Brilliant and elegant. Thank you very much Sergey. – AA. Mar 31 '14 at 17:26
1

Doesn't work for me :( `groovy.lang.MissingMethodException: No signature of method: grails.gorm.CriteriaBuilder.resultTransformer() is applicable for argument types: (org.hibernate.transform.AliasToEntityMapResultTransformer) values: [org.hibernate.transform.AliasToEntityMapResultTransformer@7a78d380]` – Zorobay Nov 05 '21 at 12:42

score 2 · Answer 2 · answered Feb 08 '12 at 05:44

2

def topFiveList = []
topFiveUsers.each { rec ->
    topFiveList << [posts: rec[0], author: rec[1]]
}

answered Feb 08 '12 at 05:44

Jim Norman

751
8
28

score 2 · Answer 3 · answered Feb 08 '12 at 05:50

2

def converted = topFiveUsers.collect{ [posts: it[0], author: it[1]] }

answered Feb 08 '12 at 05:50

Jim Norman

751
8
28

Thanks for the reply. Of course I can transform the result later on - that is what I am doing at the moment. Then again I want to skip the overhead of postprocessing the result set. I guess that somewhere in the hibernate code the very same transformation happens anyway... – fluxon Feb 09 '12 at 10:35
Shortly after I posted, it occurred to me that you weren't interested in a two-step solution. – Jim Norman Feb 09 '12 at 15:20
No worries! Thank you anyway for your support! se is just great! – fluxon Feb 09 '12 at 20:53

Grails queries with criteria: how to get back a map with column?

3 Answers3