GORM create criteria - Using 'having' clause of SQL

Question

I have a dummy cafeteria project wherein I have three domain classes: User, Product and Transaction.

Here's how the classes are defined:

class User {

    String name
    int employeeId
    long balance = 800
    static constraints = {
        balance(max: 800L)
    }   
}

class Product {

    String type 
    int quantityInStock
    float price
    static constraints = {
    }
}

class Transaction {

    int quantityBought
    static belongsTo = [user: User, product: Product]
    static constraints = {
    }
}

Now I want to find out the number of/list of users who have bought more than, say 2 products. How do I do it using Grails createCriteria?

This is what I tried:

Transaction.createCriteria().list(){
    projections {
        groupProperty('user')
        'product' {
            count('id','numberOfPurchases')
        }
    }
    gt('numberOfPurchases',2)
}

But it gives the following error:

Stacktrace:

org.hibernate.QueryException: could not resolve property: numberOfPurchases of: cafeteria.dummy.Transaction 
at grails.orm.HibernateCriteriaBuilder.invokeMethod(HibernateCriteriaBuilder.java:1618)                    
at Script1.run(Script1.groovy:4)                                                                           
at org.grails.plugins.console.ConsoleService.eval(ConsoleService.groovy:37)                                
at org.grails.plugins.console.ConsoleController.execute(ConsoleController.groovy:59)                       
at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:195)   
at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)                          
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)                         
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)                         
at java.lang.Thread.run(Thread.java:745)                                                                   

How can I access this alias numberOfPurchases outside the projections block so as to use it in gt?

Answer 1

Internally GORM uses Hibernate and currently Hibernate doesn't have support for having clause in Criteria API. The reason you are not able to get numberOfPurchases outside projections block is that the restrictions are added to where clause and you can't use an aggregation alias in where clause. Although JPA has support for having clause.

But if you still want to achieve this using GORM Criteria you can. But you would have to modify your query a little bit and it would not involve use of having clause and will require a subquery.

Modified query will be:

SELECT DISTINCT this_.user_id FROM transaction this_ where 2 < (SELECT count(sub_.id) FROM transaction sub_ WHERE sub_.user_id=this_.user_id)

And GORM for it would be:

import org.hibernate.criterion.DetachedCriteria
import org.hibernate.criterion.Projections
import org.hibernate.criterion.Restrictions
import org.hibernate.criterion.Subqueries

DetachedCriteria subQuery = DetachedCriteria.forClass(Transaction, 'sub').with {
    setProjection Projections.count('sub.id')
    add Restrictions.eqProperty('sub.user', 'this.user')
}

List<User> users = Transaction.createCriteria().list() {
    projections {
        distinct("user")
    }
    add Subqueries.lt(2l, subQuery)
}