PostgreSQL Custom Order By in Java CriteriaBuilder

Question

Id	Child_one_id	Child_two_id	Parent_id
1	2	3	null
2	null	null	1
3	2	null	1
4	null	null	null
5	6	7	null
6	null	null	5
7	6	null	5
8	null	null	null
9	null	null	null
10	11	12	null
11	null	null	10
12	null	null	10

I want to sort using orderBy in PostgreSQL Data like 10, 11,12,9,8,5,6,7,4,1,2,3, which was Id descending and if that id contains child_one_id and child_two_id then that will be followed as predecessor in sort order.

All id's have been linked in the table like the above.

currently i am retriving only id in desc order via CriteriaBuilder in Java, But in CriteriaBuilder i have additionally joined more than 10 tables, because that other table column values also needs to fetch to produce output in Java.

So it is possible with crtieriaBuilder in java to achieve this ?

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Aug 1, 2024 at 18:01

d r · Accepted Answer · 2024-08-02 09:07:21Z

You can have your own Order By if you create Root_id and Order_child_id columns like in cte grid below:

Create Table tbl as    --  S a m p l e    D a t a :
    ( Select  1 as Id, 2 as Child_one_id, 3 as Child_two_id, null as Parent_id  Union All
      Select  2,    null, null, 1     Union All
      Select  3,    2,    null, 1     Union All
      Select  4,    null, null, null  Union All
      Select  5,    6,     7,   null  Union All
      Select  6,    null, null, 5     Union All
      Select  7,    6,    null, 5     Union All
      Select  8,    null, null, null  Union All
      Select  9,    null, null, null  Union All
      Select 10,    11,    12,  null  Union All
      Select 11,    null, null, 10    Union All
      Select 12,    null, null, 10
    );

... creating two more columns (cte named grid) that shall be used in Order By clause ...

WITH 
    grid (Id, Child_one_id, Child_two_id, Parent_id, Root_id, Order_child_id) AS
      ( Select Id, Child_one_id, Child_two_id, Parent_id, 
              Greatest( Parent_id, 
                        Case When Parent_id Is Null Then Id Else 0 End 
                      ) as Root_id,
               Case When Child_one_id Is Not Null And 
                         Child_two_id  Is Not Null Then 0 
               Else Id End as Order_child_id
        From   tbl
      )

--    M a i n    S Q L : 
Select     Id, Child_one_id, Child_two_id, Parent_id
From       grid
Order By   Root_id Desc, Order_child_id

/*      R e s u l t : 
id  child_one_id  child_two_id  parent_id
--  ------------  ------------  ---------
10            11            12       null
11          null          null         10
12          null          null         10
 9          null          null       null
 8          null          null       null
 5             6             7       null
 6          null          null          5
 7             6          null          5
 4          null          null       null
 1             2             3       null
 2          null          null          1
 3             2          null          1  */

See the fiddle here.

Collectives™ on Stack Overflow

PostgreSQL Custom Order By in Java CriteriaBuilder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related