Translating SQL Query to HQL Query/Projection Criteria

rhossi · **Joined:** Fri Feb 22, 2008 9:40 am **Posts:** 7

How to translate the following SQL Query to HQL or Projection Criteria ?

Code:

select 
   e.id
   , e.num_position
   , ts.lang_id as default_lang_id
   , ts.localized_text as default_text
   , ts2.lang_id as translation_lang_id
   , ts2.localized_text as translation
from estrato e
left outer join textsnippet ts on ts.ref_id = e.id and ts.ntype = 2 and ts.lang_id = 14
left outer join textsnippet ts2 on ts2.ref_id = e.id and ts2.ntype = 2 and ts2.lang_id in (34,36)
where e.container_id = 548

I'm trying to do this, but is the first time I need to write a query in HQL/Criteria that has 2 joins on the same entity and the join clause is kinda "special".

I'm using NH 1.2 and I've written the following HQL but it's not working as expected.

Quote:

select e.Id, s.LocalizedText, s2.LocalizedText from Estrato e
left outer join e.Snippets s
left outer join e.Snippets s2
where e.Container.Id = 548 and s.Language.Id = 14 and (s2 is null or s2.Language.Id in (34, 36))

Cheers,

Felipe Garcia

wolli · **Posted:** Thu Mar 13, 2008 4:45 am

The where clause of the hql seems different then the one from your sql. Use hibernate.show_sql="true" in your configuration and have a look at the generated sql.

I'm missing "s.ntype=2 and s2.nstype=2" in the sql and I'm not sure if "s2 is null" is working (why do you use it anyway, there's nothing like that in your sql ?).