Apache Torque nested select

Question

I've got following query:

select * from situation s where s.version = 
(select max(ss.version) from situation ss where ss.situationKey = s.situationKey)

And I'd like to encode it as Torque's Criteria. The main problem here is that the subquery is linked with the main query by situationKey. I didn't find such case anywhere documented, is it possible to do this query in Torque?

Answer 1

Criteria nestedCriteria = // Your nested criteria 
String nested = BasePeer.createQueryString(nestedCriteria);
String col = // Your Peer Column, in this case "SituationPeer.VERSION"
criteria.add(col, (Object) (col + " in ( " + nested + " )"), Criteria.CUSTOM);

Your query seems to reference the outer select from the inner select. You might have to add an alias to do that. Not sure but Torque does have the nested select capability.