CODEWITHSUNDEEP
javajpaspecificationsnativequery
Tracker7
Tracker7

Reputation: 87

Is it possible to write 'with queries' in jpa specifications

I have native query written with 'with queries'. How could it be rewritten by jpa specifications? It's the small part of code

with sub_query1 as (
    select table1.id
    from table1
    join table2 ON table1.id = table2.contract_id
    where table2.administrator_id = 11
    order by table1.create_date desc
), sub_query2 as (
    select table1.id
    from table1
    join table3 on table1.id = table3.id
    where table3.administrator_id = 11
    order by table1.create_date desc
)
select table1.id
from table1    
where (table1.id in (select id from esa_subquery) or table1.id in (select id from ec_subquery));

Upvotes: 0

Views: 38

Answers (1)

Pierre Demeestere
Pierre Demeestere

Reputation: 406

Assuming there is a table1 entity, a table2 entity with contract and administrator relations and a table3 with administrator relation, you can rewrite it like that

select t from table1 where exists (select 1 from table2 t2 where t2.contract.id = t.id and t2.administrator.id = 11) or exists (select 1 from table3 t3 where t3.id = t1.id and t3.administrator.id = 11)

Upvotes: 1

Related Questions