JPA Repository nativeQuery with pageable, join table not working when pageable.size = 1

Question

I have this sql query that works fine

SELECT ppd.userId, AVG(Coalesce(sm.score, 0)) AS avgScore
    FROM Table1 ppd 
    LEFT JOIN Table2 sm ON ppd.userId = sm.userId AND sm.created BETWEEN start AND end
    WHERE ppd.someId IN (listOfIds) 
    GROUP BY ppd.userId ORDER BY avgScore DESC LIMIT 1 OFFSET 0;

I have written a query for a method in a JPARepository that looks like the following

@Query(value = "SELECT ppd.userId, AVG(Coalesce(sm.score, 0)) AS avgScore " +
            " FROM Table1 ppd " +
            " LEFT JOIN Table2 sm ON sm.userId = ppd.userId AND sm.created BETWEEN :start AND :end " +
            " WHERE ppd.someId IN (:IdList) GROUP BY ppd.userId",
            nativeQuery = true)
    Page<MyDtoProjection> getAvg(@Param("IdList") List<String> IdList,
                                 @Param("start") Long start,
                                 @Param("end") Long end,
                                 Pageable pageable);

Which works just as expected except when the pageable size is 1 (SQL query with Limit 1 works fine). For the pageable parameter the service sends the following pageable object

Sort sort = JpaSort.unsafe(Sort.Direction.ASC, "(avgScore)");
Pageable pageable = PageRequest.of(offset, limit, sort);

The request sets offset=0 and limit=1

The error I'm receiving from request using repository code is this one: Unknown column 'ppd' in 'field list'\nQuery is: select count(ppd)..., I don't know why it is taking the alias ppd as a column name, also, I read from another post that this way of executing native queries performs a count (which can be seen in the error message, I did not specify that count query), and that it can be specified by setting countQuery parameter, but I'm not sure what to write there.

score 3 · Accepted Answer · answered Sep 08 '21 at 00:18

Solve it> Just added the countQuery to the @Query annotation, I was not so sure what to place there, but it was simple

@Query(value = "SELECT ppd.userId, AVG(Coalesce(sm.score, 0)) AS avgScore " +
            " FROM Table1 ppd " +
            " LEFT JOIN Table2 sm ON sm.userId = ppd.userId AND sm.created BETWEEN :start AND :end " +
            " WHERE ppd.someId IN (:IdList) GROUP BY ppd.userId",
       countQuery = "SELECT COUNT(ppd.userId) FROM Table1 ppd " +
            "WHERE ppd.userId IN (:IdList) GROUP BY ppd.userId",
       nativeQuery = true)
    Page<MyDtoProjection> getAvg(@Param("IdList") List<String> IdList,
                                 @Param("start") Long start,
                                 @Param("end") Long end,
                                 Pageable pageable);

This is the only post I found that mentions the countQuery and how it works in one of the answers, go check it out if someone is going through the same Spring Data and Native Query with pagination

Agree. With new versions of spring countQuery was enough, worked even with aliases. (My Spring Boot verison was 2.1) — Joan, May 20 '23 at 13:12

JPA Repository nativeQuery with pageable, join table not working when pageable.size = 1

1 Answers1