Using Hibernate Criteria API To Query Many-To-One Relationships with Projections

I am trying to use Criteria API in following scenario:

• I have two tables, Schedule and Route (wi开发者_运维技巧th their classes and mappings).
• Route has many-to-one relationship with Schedule.
• Route has an integer property sequence.

Now I need to fetch all those Schedule objects whose associated Route objects fulfill the following condition:

route.sequence=no. of all Route objects associated with the given Schedule object

I have tried the following Criteria code for it:

Criteria crit = getSession().createCriteria(getPersistentClass())
    .createCriteria("routes", "route")
    .setProjection(Projections.projectionList()
    .add( Projections.rowCount(), "routeCount"))
    .add(Restrictions.not(Restrictions.ltProperty("route.sequence", "routeCount")));

But it generates the following sql:

select count(*) as y0_ 
from schedule this_
inner join route route1_ on this_.ID=route1_.scheduleId
where route1_.sequence<y0_

and throws the following error:

Unknown column 'y0_' in 'where clause'

Please help me if you have any suggestions.

The problem stems from an implementation issue with projections and restrictions. It seemed that there was a bug when trying to project and restrict on the same column - the generated sql was not valid. You will find that if run that sql directly against your database that it won't work.

The bug was originally logged here and it looked like it would not be fixed. But then I see another similar bug was logged here but I can't work out which release the fix will be available in.

The discussion that deals more with the issue and the theory behind it can be found here.

There is also another stackoverflow item dealing with the same question and offers a solution. I haven't tried to see if this approach works but it seemed to work for the people involved in the issue.