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