When using the Detached Criteria Builder is there a way to place values from the root entity into the subcriteria?
So for example if I have a Project entity and a related Task entity (all the tasks related to a project) and I want to return all the Projects that cost more than $10,000 where there have been zero tasks completed. So, this is different than having zero tasks, because there are tasks and I want to identify Projects with tasks where every task associated with that project meets a certain criteria, let’s say a date field Task.completedAt is not null.
It seems like I should do something like this:
var c = newCriteria( "Project" ); c = c.restrictions.gt( "cost", JavaCast( "int" , 10000 ) ); c = c.createSubcriteria( entityName="Task", alias="t" ).withProjections( property="project" ).isEQ( "t.ID", "this.ID" ).isNotNull( "completedAt" ).notExists(); c.list();
Which I’d think would yield some SQL like this:
`
SELECT
FROM
Project this_
WHERE
this_.Cost > 10000
AND NOT EXISTS (
SELECT
t_.ProjectID AS y0_
FROM
Task t_
WHERE
t_.ID = ? <— I’m expecting “this_.ID” to be placed here
AND t_.completedAt IS NOT NULL
)
`
But I get a Hibernate error: “could not bind value ‘this.ID’ to parameter: 1” when it tries to place the parameter I’d expect to be “this_.ID”.
When I test this instead:
.isEQ( "t.ID", JavaCast("int", 999) ).isNotNull( "completedAt" ).notExists();
I do get a value in the place I’d expect:
WHERE t_.ID = ? <--- 999 gets placed here as I'd expect AND t_.completedAt IS NOT NULL
So, doesn’t it seem like there should be a way to plug in a value from the root entity there somehow? Is there a way?
In the meantime I’m doing the following, which yields the results I want, but I feel like I had to bend to the will of the computer and I don’t like it:
var c = newCriteria( "Project" ); c = c.restrictions.gt( "cost", JavaCast( "int" , 10000 ) ); c = c.createSubcriteria( entityName="Task", alias="t" ).withProjections( property="project" ).isNotNull( "completedAt" ).propertyNotIn('ID'); c.list();
Which sends in this SQL, which works, but has no reference to the root entity in the subquery criteria:
SELECT <all the fields here> FROM Project this_ WHERE this_.Cost > 10000 AND this_.ID NOT IN ( SELECT t_.ProjectID AS y0_ FROM Task t_ WHERE t_.completedAt IS NOT NULL )