Clever Geek Handbook

Failed to convert query to HQL

I am new to HQL (well, nHibernate in general) and I am currently building a simple application to learn more about this.

I am having trouble trying to express the following SQL as HQL and would be very grateful for any ideas.

Here's the request:

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
+1


source to share


2 answers


You can't vouch for the second part, but maybe it can get closer to the goal. I've replaced the parentid comparison for a many-to-one link. Anyone 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)
0


source


Thanks - it got me on the right track. I couldn't use Top, but rewriting the query like this seemed to do 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


Sorry for the nasty string concatenation - the next step is to clean it up using parameterized HQL!

0


source






More articles:


All Articles