Spring Data JPA sorting on additional column returned by query-CodePudding

I have a Table "team" with below structure:

Column	Type	Nullable
`id`	uuid	not null
`team_name`	character varying(200)	not null
`admin_group`	uuid	not null
`lead_group`	uuid	not null
`user_group`	uuid	not null

I am using Spring Data JPA and need to execute query equivalent to:

SELECT *,
       CASE
           WHEN lead_group IN groupList THEN 3
           WHEN admin_group IN groupList THEN 2
           WHEN user_group IN groupList THEN 1
           ELSE 0 END AS role
FROM team;

Here groupList is list of all groups user is part of. I get this info from another service.

What is the best way to do it using Spring Data JPA? I also need to support sorting based on all columns and pagination. Do I add a additional field 'role' in my Team entity or create a separate class altogether to collect the data from the query?

CodePudding user response：

There are several ways to achive it:

you should define DB view using this query and define entity/repo in your code for read only.

you can use annotation Query and Pagination object in repoitory like:

@Repository
  public interface GroupTeamRepository extends 
  JpaRepository<GroupTeamEntity, UUID>, 
  JpaSpecificationExecutor<GroupTeamEntity>

      @Query(value="""
      SELECT *,
          CASE
            WHEN lead_group IN groupList THEN 3
            WHEN admin_group IN groupList THEN 2
            WHEN user_group IN groupList THEN 1
            ELSE 0 END AS role
          FROM team
      """, nativeQuery = true)
      Page<GroupTeamEntity> getGroupedTeam(Pageable pageable);

There is a really useful feature: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications

CodePudding user response：

That is a tricky one, since you state that you obtain the groupList from a different service.

For a first option lets challenge this assumption. If you instead could get the groupList through a function call or inner select from the database you can get a nice solution if you also happen to use Hibernate: Use the @Formula annotation to make the computation a field of your entity. Paging and sorting should work just fine with that approach. Alternatively you can join the information to your table using a database view and map your entity to that view. Just make sure to make the role read only.

If you really can't obtain it in the database by normal means, maybe we can do a trick: Put that information in the database before you start querying for your team. Create something, that is wrapped around your service, e.g. a web filter or an Aspect. It would obtain groupList and make it available in the database. You could simply write it into a table, using JPA or JDBC, or you could use something more fancy like for example Oracles temporary tables, which might yield performance benefits. Depending on how you make the list available in the database you can then map it to a read only field of your entity or use the @Formula annotation mentioned above.

If you really can't get the groupList in any other way then through a parameter things get a little more tricky. You'll have to resort to custom method implementations on your repository and work with JPA directly. You'd have to create your query manually including the required sorting and pagination, probably using the Criteria API. Since we are talking a single entity, the work involved for this is limited and depending on your experience with JPA on the one hand and lower level database stuff on the other hand, might even be easier than the other solutions.

Note: translating the SQL case expression to JPQL shouldn't be too difficult.