CODEWITHSUNDEEP
spring-data-jpa
Loc Le
Loc Le

Reputation: 557

Spring Data JPA sort column not in table/entity

I have query like that:

    @Query(value = "SELECT new com.domain.ActivityStatistic( " +
        "adm.id, " +
        "adm.fullName, " +
        "COUNT(CASE WHEN (act.action = 'APPROVE') THEN act.action END) AS approved, " +
        "max(act.actionTime) AS lastActionTime) " +
        "FROM Actions act, Admins adm LEFT JOIN adm.group gr " +
        "WHERE adm.id = act.adminId AND act.actionTime BETWEEN ?1 AND ?2 AND gr.id = ?3 " +
        "GROUP BY adm.id")
Page<ActivityStatistic> getActivityStatistics(LocalDateTime from,
                                                              LocalDateTime to,
                                                              long groupId,
                                                              Pageable pageable);

How can I sort it by the new field that I created: lastActionTime, approved ? I can run it by native sql in postgresql: pgadmin. But in jpa, when I using sort with field name is approved, it auto become act.approved in JPA query.

I used to read this post Spring Data and how to sort by a column not in an Entity but it not help.

Upvotes: 1

Views: 2046

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81862

You can't apply in JPQL on a table column that isn't mapped to a property of an entity. The reason for this is that JPA including JPQL operates on these entities.

Use a native query instead.

Upvotes: 1

Related Questions