Skip to content

Instantly share code, notes, and snippets.

@roalcantara
Last active March 1, 2024 16:49
Show Gist options
  • Save roalcantara/f5a27eedc14c1719d250 to your computer and use it in GitHub Desktop.
Save roalcantara/f5a27eedc14c1719d250 to your computer and use it in GitHub Desktop.
Grails: Criteria + Subquery (DetachedCriteria)
/*
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
}
@ppazos
Copy link

ppazos commented Mar 1, 2024

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment