• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

How to translate into EJB QL?

i've got problem translating following query to be eligible to use within entity manager. Basically it should take details given in language from second table in one query. If no enty, then columns are null. This works fine in SQL but have no idea how to make it work in EJB QL...

Reg. db schema, in category table category_id is PK, category_lang_detail table got category_id + language_code as PK. Relation one to many.

SELECT c.category_id, c.category_name, c.sort_order,
                    cld.category_name AS lang_name, cld.sort_order AS lang_sort_order
                    FROM category c 
                    LEFT OUTER JOIN category_lang_detail cld  
                    ON c.category_id = cld.category_id AND 
                    cld.language_code = 'EN'
                    WHERE c.category_id IN(1,2,3)
                   AND parent_category = 100 AND 
                    (cld.visible = true OR cld.visible IS NULL) 
                    ORDER BY cld.sort_order, c.sort_order ASC

Open in new window

1 Solution
I would try the following:

select c from Category c left join c.langDetail cld
where cld.languageCode = 'EN'
and c.parentCategory.id = 100
and (cld.visible = true or cld.visible is null)
order by cld.sortOrder, c.sortOrder ASC

I haven't done a ton of left joins in JPA/Hibernate, but I've done a few over the years.  Optionally, replace the parent category reference with...

and c.parentCategory = :pCat

then pass in the parent category object with "setParameter" on the query object.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now