0

In the following code I want to get the data from Order table and also from User table How can I modify my query so that I can achieve this ? user_id is foreign key in order table 

public interface OrderRepository extends JpaRepository<Order, Long> {
 @Query("Select o from Order o where o.customer.id= :customerId and o.orderStatus='DELIVERED'")
     List<Order> orderHistory(@Param("customerId") long customerId);
     }
Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
SFAH
  • 624
  • 15
  • 35
  • 1
    [Relevant question already being answered here, please check](http://stackoverflow.com/questions/24638465/selecting-from-multiple-tables-in-spring-data) – Rizwan May 10 '17 at 06:23
  • And how do we know the relation between these 2 CLASSES? JPA (and JPQL) uses CLASSES not TABLES. – Neil Stockton May 10 '17 at 06:32
  • 2
    Possible duplicate of [Selecting from Multiple Tables in Spring Data](http://stackoverflow.com/questions/24638465/selecting-from-multiple-tables-in-spring-data) – Neil Stockton May 10 '17 at 06:33

2 Answers2

1

To fetch the Customer with Order, do the join fetch.

The JOIN FETCH expression is not a regular JOIN and it does not define a JOIN variable. Its only purpose is specifying related objects that should be fetched from the database with the query results on the same round trip. Using this query improves the efficiency of iteration over the result Country objects because it eliminates the need for retrieving the associated Capital objects separately. http://www.objectdb.com/java/jpa/query/jpql/from

public interface OrderRepository extends JpaRepository<Order, Long> {
 @Query("Select o from Order o inner join fetch o.customer as customer left join fetch o.user as user where customer.id= :customerId and o.orderStatus='DELIVERED'")
     List<Order> orderHistory(@Param("customerId") long customerId);
     }
Sudhakar
  • 3,104
  • 2
  • 27
  • 36
  • Why inner and left join both ? – SFAH May 10 '17 at 06:30
  • To load customer who have orders used inner join. Not sure whether your schema user is mandatory for order, so used left join. If your order table always has user_id, Please change left join to inner join. – Sudhakar May 10 '17 at 06:33
0

You want to put customerId and order fields into param? I think list the order fields in params is enough. of course sql statement must be right.

astrologer
  • 11
  • 1