Need help with HQL query

Question

I have a simple class, called Person, as follows:

public class Person {
    private Integer id;
    private String name;
    private List<PersonState> states;
    ...
}

And each PersonState is defined like:

public class PersonState {
    private Integer id;
    private State state;
    private Date date;
    ...
}

And State is a simple class, containing only an id and name, and I have all the mapping files configured and working.

I want to get those Persons whose last PersonState (based on it's date) has an State with id = 5 (for instance), so what would be the HQL query like? Thanks!

Answer 1

Something like this:

SELECT p FROM Person p JOIN p.states ps1 
WHERE 
    ps1.date = (SELECT MAX(ps2.date) FROM PersonState ps2 WHERE ps2 MEMBER OF p.states)
    AND ps1.state.id = ?