JPA Nested Select Left Outer Join

Question

Is it possible to do a nested select in JPA QL?

How would I write the following SQL statement in JPA QL?

select * from preferences p left outer join (select * from preferencesdisplay where user_id='XXXX') display on ap.pref_id=display.pref_id;

The JPA entity PREFERENCES has a OneToMany relationship to PREFERENCESDISPLAY. I want to get all the PREFERENCES whether or not there is a PREFERENCESDISPLAY reference.

Answer 1

Does that SQL even work?

It would help if you showed your Entities, however if you have everything correctly mapped it should be this easy:

select p from Preferences p left join p.preferencesDisplays pd with pd.userId =XXXX

or if with isn't supported by TopLink

select p from Preferences p left join p.preferencesDisplays pd
where pd IS NULL or pd.userId = XXXX

Answer 2

In hibernate you can use "with":

select ... from Preferences p left join p.displays d  with  d.user.id = 100

But in JPA I have never seen such possibilities.