Grails: Criteria + Subquery (DetachedCriteria)

sub_query.groovy

/*
Suppose we need a query searching for all Equipments that are not being used in any ServiceOrder.

It means, given the following query:

select this_.* 
from  equipment this_ 
where this_.status == 'enabled'
      and not exists (select so_.id as y0_ from service_order so_ where (so_.equipment_id=this_.id))
order by this_.serial_number asc;
*/

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

def result = Equipment.createCriteria().list() {
  eq 'status', 'enabled'
  def subQuery = DetachedCriteria.forClass(ServiceOrder, 'so').with {
                        // setProjection Projections.count('so.id')
                        setProjection Projections.id()
                        add Restrictions.conjunction()
                              .add(Restrictions.eqProperty('so.equipment.id', 'this.id'))
  }
  add Subqueries.notExists(subQuery)
  order 'serialNumber', 'asc'
  cache: true
}

Thanks!!!

You are a saint!

Awesome, this gist is a gem!

Change this setProjection Projections.count('so.id')
To this setProjection Projections.id()

The count = 0 won't result in a not exists being true, an empty result will.

Change this setProjection Projections.count('so.id')
To this setProjection Projections.id()

The count = 0 won't result in a not exists being true, an empty result will.

Wow, thank you so much for the suggestion!

I'm curious, could this behavior be due to a change in the API?
Either way, I'll definitely update the gist accordingly.

Thanks a ton!

@roalcantara check the query:

This select this_.* can't be generated by Projections.count('so.id'), it's generated by Projections.id().

I double tested it. It's not the API, seems to be an issue when copying and pasting, since this Projections.count('so.id') will generate select count(this_.*). I think the API always worked that way, since it doesn't make sense that count(id) will generate id as a projection.