having trouble converting a query to hql

Question

Hi

I'm pretty new to HQL (well, nhibernate in general) and am currently building a simple app to learn more about it.

I've run into problems trying to express the following sql as hql though, and would be very grateful for any ideas.

Here's the query:

select * from parent p where p.id in (select p.parentid from child c where c.createdby = 2) and (select top 1 createdby from child where parentid = p.id order by createdon desc) != 2

Answer 1

Can't vouch for the second part but perhaps this can get you closer to the goal. I replaced the the parentid comparisons for a many-to-one reference. Any should work in hql.

select p from parent p 
    where p in (select c.ParentReference from child c 
        where c.createdby = :twoparameter)
    and :twoparameter = (select top 1 c.createdby from child 
        where c.ParentReference = p order by p.createdon desc)

Answer 2

Hi Cristian

Thanks - that got me on the right track. I couldn't use "Top" but rewriting the query like this seems to have done the trick:

select p from Parent p where p.ID in (select c.parent.Id from Child c where c.CreatedBy = "+user.ID+") and "+ user.ID +" = (select max(c.CreatedBy) from Child c where child.parent.ID = parent.ID

Excuse the nasty string concatenation - next step is to clean this up using parameterized HQL!