Spring JPA - Issue while sorting on non entity column

Question

I have requirement where I need to get the records based in join of three table with pagination(addition requirement are also there). So I have written a nativeQuery to get the records. Below is the sample query

@Query(value = "SELECT "
        + "a.GROUP_REF_ID as refId "
        + "count(case when c.STAT_CD in :userStatus then (c.grp_user_id) end) as numberOfUsers, "
        + "count(case when b.STAT_CD in :itemStatus then (b.grp_item_id) end) as numberOfItems  "
        + "from grp a left join grp_item b on a.grp_id=b.grp_id left join grp_user c on a.grp_id=c.grp_id "
        + "where a.stat_cd in :status and a.co_id in :cids "
        + "group by a.GROUP_REF_ID,a.grp_nam,a.GRP_DESC,a.co_id,a.co_nam,a.CRTE_BY, "
        + "a.CRTE_DT,a.UPDT_BY,a.UPDT_DT ", countQuery = "select count(*) from grp where stat_cd in :status and co_id in :cids ", nativeQuery = true)
public Page<Object> findByStatusAndCompanyIdIn(@Param("status") String status, @Param("cids") List<Long> companyIds,
        @Param("userStatus") List<GroupUserStatus> userStatus,
        @Param("itemStatus") List<GroupItemStatus> itemStatus, Pageable pageable);

Now the requirement is also that these records are to be sorted on any of the column in select part. So, if user passes numberOfItems, the records are to be sorted on it. But I am facing an issue here because if I pass Sort parameter as numberOfItems, spring prepends an a. before numberOfItems which results in error that not able to find a.numberOfItems.

Is there a way I can stop spring from prepending table alias while creating a query with Sort, or should I write my logic in a different approach

You are pretty close to the limits of what Spring Data is able to handle. You might try to wrap the whole select in another one: select * from (<your current select) x — Jens Schauder
– Jens Schauder, Commented Oct 14, 2019 at 8:14

Jens Schauder · Accepted Answer · 2019-10-14 10:17:45Z

2

Making my comment an answer so the question can be marked as answered:

Wrap the whole select in another one: select * from (<your current select>) x

answered Oct 14, 2019 at 10:17

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bombe · Accepted Answer · 2020-03-21 21:47:03Z

0

I have solved the issue by creating a projection. (Kotlin was used but you’ll get the gist.)

class ShowRepository : JpaRepository<Int, Show> {
    @Query("SELECT s AS show, (CASE WHEN (s.status = 'scheduled') THEN s.scheduledStartTime ELSE s.startTime END) AS time FROM Show s")
    fun findShows(pageable: Pageable): Page<ShowWithTime>
}

interface ShowWithTime {
    val show: Show,
    val time: Date?
}

This allows Spring-Data to work its full magic, and using Sort.by(Order.desc("time")) works like a charm.

I’ve written it up with a little bit more detail here: Sorting by a Non-Entity Field.

answered Mar 21, 2020 at 21:47

Bombe

84.4k20 gold badges127 silver badges127 bronze badges

Collectives™ on Stack Overflow

Spring JPA - Issue while sorting on non entity column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related