0

I am trying to use Criteria API in following scenario:

  • I have two tables, Schedule and Route (with 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.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
craftsman
  • 15,133
  • 17
  • 70
  • 86
  • I don't understand your pseudocode-condition. Do you actually mean that all routes need to have the same sequence number? – meriton Nov 19 '09 at 20:49
  • One Schedule object can have n Route objects. (where n is 2 or 3 usually ). The value of Route.sequence can range from 0 to n-1. Actually it is a Schema for a simple flight information system. Schedule table contains information such as flight number, schedule date/time and flight status. The cities that make up the route of the flight are put into Route table (since there can be variable number of route cities for each flight). Route.sequence number identifies the sequence in which a city lies for a flight. – craftsman Nov 20 '09 at 07:15
  • The query I have been trying to make up with Criteria API is to search for all flights that arrive at a given city X. – craftsman Nov 20 '09 at 07:17

1 Answers1

1

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.

Community
  • 1
  • 1
Rachel
  • 3,691
  • 5
  • 32
  • 32