Do left outer join only if condition satisfies in JPA

Question

I am new to JPA and trying to do a left outer join, only if a condition is satisfied in the native JPA query.

Native query is as shown below. Here I would like to do A left outer join B with the where condition, only if a given flag toJoin is true in application:

select a.id, a.name from A left outer join B on A.id = B.aid where b.name="some_name"

I am not sure, how will I accommodate the toJoin application flag in the above native JPA query in my JPA repository.

EDIT

I was just trying to achieve the same in mysql with the help of CASE WHEN and sample condition as follows:

select a.id from A a, case when 1=2 then left outer join B b ON a.id = b.aid end limit 2;

But I am getting syntaxt error.

@maSTAShuFu I am not sure, how will I write the IF clause in my JPA repository. If possible, could you please give a short example? Thanks. — Joy, Oct 21 '21 at 03:18
https://stackoverflow.com/questions/14097652/conditional-where-clause-in-jpa-criteria-query — RoMEoMusTDiE, Oct 21 '21 at 03:21
@maSTAShuFu I went through the link. But as of now, all queries in the JPA repository are native, wherein several queries I have been doing the changes. Criteria builder has not been used so far. I am thinking, in order to follow the approach as suggested in the link, I have to make a considerable amount of changes. So I am wondering if there is any way to achieve this with the help of native query. — Joy, Oct 21 '21 at 03:26
Actually not all the rows in A, associated with a row in B. In the result, I want all the rows in A, regardless if it matches or not with a row in B. That's why I need to do left outer join. I want to do this left outer join only if the toJoin flag is true in the application as mentioned in OP. — Joy, Oct 21 '21 at 03:48
when 1=2, is just a representative condition. Its intent is to skip the join when the associated condition is false. — Joy, Oct 21 '21 at 04:31
*I would like to do A left outer join B with the where condition, only if a given flag toJoin is true in application* Noway in SQL. The output structure is always definite and static whereas you want to obtain different output structure depends on the condition. Solution - test this flag in the application and execute the query either with or without JOINing depends on the flag value. — Akina, Oct 21 '21 at 05:09
Since you are not selecting from table B, why join to it? Did you mean to `select a.id, b.someColumn` ? — Bohemian, Oct 21 '21 at 06:13

score 0 · Answer 1 · answered Oct 21 '21 at 10:58

Your question remains vague, however I suspect that your effort:

select a.id
from A a, case when 1=2 then left outer join B b on a.id = b.aid end
limit 2

Is meant to be:

select a.id, b.someColumn
from A a
left join B b on a.id = b.aid and 1=2
limit 2

Where 1=2 is actually some more complicated expression.

Do left outer join only if condition satisfies in JPA

1 Answers1