How to transform SQL (nested subquery) to query created by QueryBuilder

Question

Is this possible to create a query by JPA QueryBuilder using query.from(subquery)?

select DISTINCT substring(min(sel."order"), 2)
from
  (SELECT s.name || d.name "name",
          CASE
          WHEN d.status = 'ACCEPTED' then '0ACCEPTED'
          WHEN d.status = 'OPEN' then '1OPEN'
          WHEN d.status = 'DISMISSED' then '2DISMISSED'
          end "order"
   FROM items AS d
     LEFT JOIN store s ON d.store_id = s.id
   WHERE ... ) sel
GROUP BY sel.name

please don't try to see logic in the query

I just want to know how to create such construction by query builder

select * 
from (select something 
      from some table
      where conditions)

if it is possible of course

I know how to use a subquery in the WHERE or the SELECT section. but I don't know how to use subquery inside the FROM expression

Possible duplicate of [JPA 2.0, Criteria API, Subqueries, In Expressions](https://stackoverflow.com/questions/4483576/jpa-2-0-criteria-api-subqueries-in-expressions) — Simon Martinelli, May 22 '19 at 15:14
@SimonMartinelli no, because I know how to use subquery in the WHERE or the SELECT section. but I don't know how to use subquery inside the FROM expression — JVic, May 22 '19 at 15:17

score 1 · Accepted Answer · answered May 22 '19 at 17:15

1

It's not possible to use plain SQL with Criteria API.

the Criteria API is working on the Entities not on the database tables.

answered May 22 '19 at 17:15

Simon Martinelli

34,053
5
48
82

How to transform SQL (nested subquery) to query created by QueryBuilder

1 Answers1