CODEWITHSUNDEEP
javasqldatabasehibernatejpql
Priyank Doshi
Priyank Doshi

Reputation: 13151

conditional left outer join SQL query To JPQL

SQL FIDDLE LINK FOR THIS DATA

What is a jpql equivalent of following SQL query :

select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=1;

Sample Data:

ij> select * from App;
ID         |STATUS
----------------------
1          |active
2          |active1
3          |active3
5          |active

4 rows selected
ij> select * from App_Child;
ID         |STATUS    |D
----------------------------------
1          |active    |1
2          |active11  |2
1          |active111 |3
1          |active    |4

4 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=1;
ID         |STATUS    |ID         |STATUS    |D
---------------------------------------------------------
1          |active    |1          |active    |1
1          |active    |1          |active    |4

2 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=5;
ID         |STATUS    |ID         |STATUS    |D
---------------------------------------------------------
5          |active    |NULL       |NULL      |NULL

1 row selected

EDIT : We are using jpa 2.0

Upvotes: 7

Views: 4818

Answers (2)

Ben Thurley
Ben Thurley

Reputation: 7141

If you want a SQL query to find all App's that have an "active" App_Child you could try exists instead of join. 

-- Alternative SQL to join
select a.* from App a where a.ID = 1 and exists (select * from App_Child b where a.id=b.id AND b.STATUS = 'active')

In your example you had two things going on. In the SQL example on this page you're just getting the columns from the App table. On your fiddle though you're getting columns from the App table as well as the App_Child with only the "active" child rows shown. This exists method will work for the first query where you just want to retrieve the App but if you want to fetch both the App and child together then it won't help.

What you could do though is add a method to the App entitiy to get a collection of Active App_Child and map an appropriate property. You can get the App's you want using this "exists" query and then call getActiveChildren on each App.

I tested the SQL on your fiddle, here is the edit: http://sqlfiddle.com/#!4/0f39a/6/2

This reference shows you exists in JPQL. http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html#jpa_langref_exists

Hopefully that's enough info for you to try it out.

Upvotes: 0

Saintali
Saintali

Reputation: 4571

The condition App.id=App_Child.id is automatically added because of the @ManyToOne relation. And in JPA 2.1, you can add additional conditions with an explicit on clause:

select a 
from App a left outer join 
     a.children c on (c.status = 'active') 
where a.status='active' and a.id=1;

See for example EclipseLink Documentation.

Upvotes: 8

Related Questions