Does Hibernate (HQL) Support Common Table Expression

Question

I have a query that looks like:

WITH SubQ AS
    (SELECT elh.encntr_id, elh.location_cd
     FROM encntr_loc_his elh
     WHERE ...)
SELECT e.encntr_id
FROM encounter e
WHERE e.location_cd IN
    (SELECT SubQ.location_cd
     FROM...)
...

There are some other details in this query, and the SubQ has been used a lot. My question is, is it possible to put this query in HQL as a named query(namedquery)? When I try to do that and compile, it throws an error complaining about token WITH:

Jun 19, 2017 10:38:58 AM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: line 1:1: unexpected token: WITH Jun 19, 2017 10:38:58 AM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: line 1:1: unexpected token: WITH line 1:1: unexpected token: WITH

Answer 1

Hibernate doesn't support common table expressions, but if you want to be able to reference your SubQ query so you don't have to repeat it, you could define it as a view on the database and then map a Hibernate entity to that view.

Answer 2

Hibernate 6.2 now supports CTE (https://in.relation.to/2023/02/20/hibernate-orm-62-ctes/)