How can I express this in HQL

I've been stuck with this query for some time now. The SQL returns the result that I want, but I cannot work out how to express the query in HQL.

Here's the SQL:

select   thread.ThreadId,
      thread.Title,
      thread.CreatedOn, 
      thread.ViewCount,
      thread.CreatedBy,
      thread.ForumId 

from Threads thread 
where 
    (thread.ThreadId in(select post.ThreadId from Posts post 
            where (post.CreatedBy=2 )))

AND

(2!=(select TOP 1 post2.CreatedBy from Posts post2 
     where (post2.ThreadId=thread.ThreadId ) ORDER BY post2.CreatedOn DESC))

I think I need to use DetachedCriteria to create the correlated subquery, but I'm having real problems.

Here's where I am so far:

DetachedCriteria latestPostInThread = DetachedCriteria.For(typeof (ForumPost), "post2")
      .Add(Expression.EqProperty("post2.ThreadId", "post.ThreadId"))
      .AddOrder(Order.Desc("CreatedOn"))
      .SetFirstResult(0)
      .SetMaxResults(1);

and for the main query:

ICriteria critMain = CreateCriteria(typeof (ForumPost), "post")
      .Add(Expression.Eq("CreatedBy",user.ID))
      .Add(Subqueries.Ne("CreatedBy", latestPostInThread));

This is clearly not correct - I need to get the userId from latestPostInThread in order to use it as my subquery, but I'm utterly stumped.

Any help would be hugely appreciated! I'm loathe to fall back to SQL for somethign that I'm sure is not particularly difficult to achieve in nHibernate.

Thanks for looking

Graham

ansaurus

tags:

views:

answers:

How can I express this in HQL

related questions