Grails criteria: how to count results from a grouped subquery?

Question

Ok guys I'm finally stuck with GORM Criteria API implementation facing this problem:

I've got an Oracle view mapped like this:

class MyView implements Serializable {
    Long idA
    Long idB
    Long colA1
    String colA2
    String colA3
    String colB1
    String colB2

    static mapping = {
        id composite: ['idA', 'idB'], generator: 'assigned'
    }
}

What I need here would be to search/filter over this domain possibly on

colA1, colA2, colA3, colB1, colB2

But I need to effectively fetch distinct/grouped result sets of:

colA1, colA2, colA3

On associated request, for pagination, I need to fetch:

• count of fetched values
• a defined part of filtered results

The problem is on combining counting and grouping. If I build a request like this:

MyView.withCriteria({
    projections {
        groupProperty('colA1')
        groupProperty('colA2')
        groupProperty('colA3')
    }

    // ... Restrictions on MyView properties
})

Would you have some trick to get a count of associated result? I saw many limitations on Hibernate-criteria API and I'm out of ideas so I will really appreciate your help! :-)

Answer 1

Ok so I finally figured out my problem and the short answer would be: I can't.

The fact is that I both need to group my results and to count these. Although terrible in terms of performance, this would have to be done by wrapping the "group" request by a select count(*).

But here's what hibernate says documentation:

Note that HQL subqueries can occur only in the select or where clauses.

So what I needed was not possible using hibernate.

I solved my issue with heavier changes implying better modelisation based on materialized views to avoid disastrous performance issues.