0

I have mysql query for joining 3 tables, how can i convert this into JPA with pagination supported. query : 

SELECT promotion.name, 
       promotion.promotion_type, 
       promotion.start_date, 
       promotion.end_date, 
       promotion_history.`count`                 AS order_count, 
       Count(user_promotion_history.promotionid) AS user_count 
FROM   promotion 
       INNER JOIN user_promotion_history 
               ON promotion.promotionid = user_promotion_history.promotionid 
       INNER JOIN promotion_history 
               ON promotion.promotionid = promotion_history.promotionid 
WHERE  promotion.status = "created" 
GROUP  BY promotion.name, 
          promotion.promotionid, 
          promotion.promotion_type, 
          promotion.start_date, 
          promotion.end_date, 
          order_count;

I have created all entity and repository classes and used native query,

@Query(value = "select promotion.name, promotion.promotion_type, promotion.start_date, promotion.end_date" +
           " promotion_history.count as order_count, count(user_promotion_history.promotionid) as user_count" +
           " from promotion" +
           " inner JOIN user_promotion_history on" +
           " promotion.promotionid = user_promotion_history.promotionid" +
           " inner join promotion_history on" +
           " promotion.promotionid = promotion_history.promotionid" +
           " where promotion.status = :status" +
           " group by" +
           " promotion.name, promotion.promotionid, promotion.promotion_type, promotion.start_date,promotion.end_date, order_count",
nativeQuery = true)
List<Object[]> findByStatus(@Param("status")PromotionStatus status);

but getting an error like this.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.count as order_count, count(user_promotion_history.promotionid) as user_count f' at line 1

Yogesh Prajapati
  • 4,770
  • 2
  • 36
  • 77
vinay aradhya
  • 1
  • 1
  • Possible duplicate of https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination – Femi Jun 14 '20 at 03:28
  • There is a missing comma between ***promotion.end_date*** and ***promotion_history.count as order_count***. But this error is not related to pagination. This is a syntax error. There are possible different ways to implement pagination (pagination)[https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging]. Also read key-set pagination https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/ – Mohd Waseem Jun 14 '20 at 07:14
  • thanks. joining working fine now. for pagination have used below approach, but still getting error, pls help me in that. – vinay aradhya Jun 14 '20 at 17:39

1 Answers1

0

For pagination i have used Pageable parameter. @Query(value = "select promotion.name, promotion.promotion_type, promotion.start_date, promotion.end_date," + " promotion_history.count as order_count, count(user_promotion_history.promotionid) as user_count" + " from promotion" + " join user_promotion_history on" + " promotion.promotionid = user_promotion_history.promotionid" + " join promotion_history on" + " promotion.promotionid = promotion_history.promotionid" + " where promotion.status = ?1" + " group by" + " promotion.name, promotion.promotionid, promotion.promotion_type, promotion.start_date,promotion.end_date, order_count \n-- #pageable\n", nativeQuery = true) List findByStatus(String status, Pageable pageable);

when i see query looks like this, Hibernate: select promotion.name, promotion.promotion_type, promotion.start_date, promotion.end_date, promotion_history.count as order_count, count(user_promotion_history.promotionid) as user_count from promotion join user_promotion_history on promotion.promotionid = user_promotion_history.promotionid join promotion_history on promotion.promotionid = promotion_history.promotionid where promotion.status = ? group by promotion.name, promotion.promotionid, promotion.promotion_type, promotion.start_date,promotion.end_date, order_count -- #pageable order by join.user_count desc limit ?

error says : org.hibernate.engine.jdbc.spi.SqlExceptionHelper Unknown column 'join.user_count' in 'order clause'

somewhere "join." is getting added in order by clause.

vinay aradhya
  • 1
  • 1