Spring Data JPA sort column not in table/entity

Question

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.

I resolved this problem by using native query, and create new Entity (without create a table) with property: approved, lastActionTime. And to sort with pageable I using table name instead of table alias name (ex: using admins instead of adm. And it work. — Loc Le
– Loc Le, Commented Jun 28, 2018 at 4:03

Jens Schauder · Accepted Answer · 2018-06-25 09:19:40Z

1

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.

answered Jun 25, 2018 at 9:19

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

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

Collectives™ on Stack Overflow

Spring Data JPA sort column not in table/entity

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related