CODEWITHSUNDEEP
springspring-bootspring-data-jpaspring-data
Ashishkumar Singh
Ashishkumar Singh

Reputation: 3600

Spring JPA - Issue while sorting on non entity column

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

Upvotes: 2

Views: 1529

Answers (2)

Bombe
Bombe

Reputation: 83846

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.

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81862

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

Upvotes: 2

Related Questions