Hibernate HQL : where clause with list

I am a little confused with Hibernate. My problem is the following:

I have an object in a table on my database, and this object is associated to different comments (stored in another table)

In HQL, I want to select those object in my table, but only if the last comment associated to the object has a particular status (the last comment is the one which has the highest ID). One comment is associated to only one object.

This request works fine:

    select distinct myObject from org.MyClass myInstance
    join myObject.comments comment
    where comment.status in (:theListOfStatusThatIWant)

But that request doesn't do what I want...

I have tried this one :

     select distinct myObje开发者_开发知识库ct from org.MyClass myInstance
     join myObject.comments comment
     where comment.status in (:theListOfStatusThatIWant)
     order by comment.id desc

But without success... (the request works fine but doesn't do what i want)

Can anybody help me ?

You need to express the fact that you want the last comment, something like this:

select distinct myObject from org.MyClass myInstance
join myObject.comments comment
where comment.status in (:theListOfStatusThatIWant)
and comment.id = 
    (select max(c1.id) from Comment c1 where c1 member of myObject.comments)

One initial thought on your query is to reverse what you are querying on. In pseudo-HQL:

select c.objectYouReallyWant from Comment c where c.status = ? order by c.id desc

You can then set maxResults (database dependent I believe) to get only a single row back. Unfortunately, this will only return the last comment with the right status. If you want to get the last comment, regardless of status, you'll need to drop the status constraint.

A limit (aka max results) should be cheaper in execution than a subselect to find the max of a particular row), but of course you'd need to test it out.