I want to user orderBy by giving specific order of role-CodePudding

I've roles like lead > collaborator > participant > viewer. If I use orderBy(role) then getting order as collaborator > lead > participant > viewer If I do DESC order then getting reverse.

I tried specifying order in SQL and it's working as expected getting lead > collaborator > participant > viewer

ORDER BY ROLE = 'viewer', ROLE = 'participant', ROLE = 'collaborator', ROLE = 'lead';

The same I want to achieve in Java Spring Boot. How can I?

CodePudding user response：

If your roles are static (seldom if ever changes) then you can accomplish this by creating a enum (Create Emun and Alter Type enum ) for them. (see demo)

create type role_enum as enum ('collaborator', 'lead', 'participant', 'viewer');
 
create table test ( id   integer  generated always as identity
                                  primary key 
                  , name text
                  , role role_enum
                  ) ;

I am not sure how to accomplish this in your obscurification manager (Spring Boot) so you will have to translate it or use raw sql.

CodePudding user response：

SELECT t FROM Table t ORDER BY (CASE role WHEN 'viewer' THEN 1 WHEN 'participant' THEN 2 WHEN 'lead' THEN 3 WHEN 'collaborator' THEN 4 ELSE 5 END) ASC