How to make query using JPA

Question

everyone. I have 3 tables: 1:

id, 
table2_id

2:

id, 
param1

3:

id, 
table1_id,
param2

I have this sql code:

select * from table1 g
left join table2 f on g.table2_id = f.id
left join table3 k on k.table1_id = g.id
where f.param1 = '1272' or k.param2= '1272'

my sql code means, that i would like take all objects from table1 by param '1272', that I'm using for other tables.

my @Query looks like:

@Query("select p from Table1 p " +
       "left join p.table2 t " +
       "left join p.table3 b " +
       "where (:param != null or t.param1 = :param or b.param2 = :param")
    Page<Table1> searchByParam(@Param("param") String param, Pageable pageable);

But Its given me wrong result. What am I doing wrong?

Answer 1

I think you missed on conditions

@Query("select p from Table1 p " +
   "left join table2 t on t.table2_id = p.id" +
   "left join table3 b on b.table1_id = p.id" +
   "where (:param != null or t.param1 = :param or b.param2 = :param")
Page<Table1> searchByParam(@Param("param") String param, Pageable pageable);

You can refer to this example

@Query(value="select s.service_request_id,s.company_name,s.product_name,"
        + "a.complete_address,a.current_location,"
        + "a.pin_code,s.user_id,u.first_name,u.last_name,u.email_id,u.phone_number,p.parts_name,p.price,p.service_charge,p.qty,p.parts_description"
        + " from service_request s INNER JOIN address a ON s.address_id=a.address_id INNER JOIN user u ON u.user_id=s.user_id INNER JOIN order_details o ON o.service_request_id=s.service_request_id INNER JOIN parts p ON p.order_id=o.order_id and s.service_request_id=?1", nativeQuery = true)
List getInvoiceDetails(int serviceRequestId);