Question 90269 in PostgreSQL Question posted in
The official documentation can be found here.

I want to user orderBy by giving specific order of role – Postgresql

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?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I've achieved this by specifying the order

    .orderBy(field("role").sortAsc("lead", "collaborator", "participant", "viewer"))

  2. 0

    You have to create this method in your service :

    public List<ConcoursAdministratifRe> getListByRole() {
String queryRecherche = "SELECT t FROM YourEntityName 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";
TypedQuery<YourEntityName > query = em.createQuery(queryRecherche, YourEntityName.class);
List<YourEntityName> myList = query.getResultList();
return myList;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search