-
-
Save roalcantara/f5a27eedc14c1719d250 to your computer and use it in GitHub Desktop.
/* | |
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 | |
} |
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.
Thanks!!!