Month: February 2014

A few years ago I wrote about dynamically building criteria queries in Grails.  Well the use case we had disappeared and I didn't do much of anything with dynamic criteria queries until recently.  As I've mentioned in an update on that post, you probably just want to use DetachedCriteria now instead of taking this route, but DetachedCriteria don't support the same range of functionality that normal criteria queries do.

I ran into this limitation when I needed to use subqueries which DetachedCriteria do not support but standard criteria queries do.  After spending hours trying to work around the limitation and still use DetachedCriteria I decided to give up and create a nice way of using the dynamic criteria queries based on my past experience, the comments on that post, and my greatly enlarged knowledge of Grails.

So I created a nice clean class called "CriteriaAggregator":

package org.example.package
import grails.orm.HibernateCriteriaBuilder

// Aggregate query criteria for a Domain Class
// Example: def qa = new CriteriaAggregator(MyDomainClass)
//    qa.addCriteria { idEq(12345L) }
//     def results = qa.get()
public class CriteriaAggregator {
    private Class forClass
    private List<Closure> criteriaClosures

    // forClass should be a Grails DomainClass; but since Grails injects rather than inherits I can't specify the type better than "Class"
    public CriteriaAggregator(Class forClass) {this.forClass = forClass; criteriaClosures = new ArrayList<Closure>(10)}

    // criteriaClosure is the exact same type of closure you'd pass to DomainClass.withCriteria(criteriaClosure)
    public void addCriteria(Closure criteriaClosure) {criteriaClosures << criteriaClosure}

    public long count() {return runQuery('get') {projections {rowCount()}}}
    public def get(Closure additionalCriteria=null) {return runQuery('get', additionalCriteria)} // Query must return only a single row
    public def list(Closure additionalCriteria=null) {return runQuery('list', additionalCriteria)}

    private def runQuery(String method, Closure additionalCriteria=null) {
        HibernateCriteriaBuilder criteriaBuilder = forClass.createCriteria()
        def critClosures = criteriaClosures // Bizarre that criteriaClosures won't evaluate properly inside the "$method" closure, but it won't so this works around that issue
        criteriaBuilder."$method" {
            critClosures.each{closure -> closure.delegate = criteriaBuilder; closure()}
            if (additionalCriteria) {additionalCriteria.delegate = criteriaBuilder; additionalCriteria()}
        }
    }
}

This wraps and defers the creation of the normal criteria builder allowing you to build up the criteria dynamically and execute it when desired.

Suppose you had a Customer domain class with many Orders.  Orders have a date, paymentMethod, and totalPrice.

def customerQueryAggregator = new CriteriaAggregator(Customer)
customerQueryAggregator.addCriteria {
  orders {
    def now = new Date()
    between('date', now-7, now)
  }
}
customerQueryAggregator.addCriteria {
  orders {
    eq('paymentMethod', 'cash')
  }
}

def numCustomersInPastWeekPayingCash = customerQueryAggregator.count()
println numCustomersInPastWeekPayingCash

def highRollersInPastWeek = customerQueryAggregator.list{
  orders {
    gt('totalPrice', 50000)
  }
}
println highRollersInPastWeek

This is obviously a trivial example where you don't need to use the aggregator.  But it's just to illustrate the usage.  I'm using it to build up a query based on a set of options provided by a caller as part of a reusable and flexible service.

Using this I can handle subqueries like so:

import grails.gorm.DetachedCriteria as GrailsDetachedCriteria
import org.hibernate.criterion.Subqueries
import grails.orm.HibernateCriteriaBuilder

def customerQueryAggregator = new CriteriaAggregator(Customer)

GrailsDetachedCriteria largeOrderSubquery = new GrailsDetachedCriteria(Order).build {
  eqProperty 'order.id', 'this.id'
  gt 'totalPrice', 50000
  projections {property 'id'}
}

customerQueryAggregator.addCriteria {
  add(Subqueries.exists(HibernateCriteriaBuilder.getHibernateDetachedCriteria(largeOrderSubquery)))
}

Update April 4, 2014:

I ended up having some trouble with the proper definition of table aliases (it always wanted to call "order" "this" even though we already had a "this") while handling the GrailsDetachedCriteria subqueries.  To work around it I ended up dropping back to straight Hibernate, but it still works with the CriteriaAggregator.

import org.hibernate.criterion.DetachedCriteria as HibernateDetachedCriteria
import org.hibernate.criterion.Restrictions
import org.hibernate.criterion.Disjunction
import org.hibernate.criterion.Subqueries
import org.hibernate.criterion.Projections

def customerQueryAggregator = new CriteriaAggregator(Customer)

HibnerateDetachedCriteria largeOrderSubquery = new HibernateDetachedCriteria.forClass(Order.class, 'order')
largeOrderSubquery.add(Restrictions.eqProperty('order.id', 'this.id'))
largeOrderSubquery.add(Restrictions.gt('order.totalPrice', 50000))
largeOrderSubquery.setProjection(Projections.property('order.id'))

customerQueryAggregator.addCriteria {
  add(Subqueries.exists(largeOrderSubquery))
}