CODEWITHSUNDEEP
postgresqlsortinghierarchical-data
Piotr Olaszewski
Piotr Olaszewski

Reputation: 6204

Sorting columns with hierarchy and additional column for sorting

I have following structure:

id,
name,
parent_id,
order_by

and entries:

 id |    name   | parent_id | order_by 
----+-----------+-----------+----------
  8 | Cat 1     |           |        1
  7 | Cat 2     |           |        2
  5 | Cat 3     |           |        3
 15 | Cat 1.1   |         8 |        1
 17 | Cat 1.2   |        15 |        2
 16 | Cat 2.1   |         8 |        1
 20 | Cat 1.2.1 |        17 |        1

And I wanna output:

 id |    name   | parent_id | order_by 
----+-----------+-----------+----------
  8 | Cat 1     |           |        1
 15 | Cat 1.1   |         8 |        1
 17 | Cat 1.2   |         8 |        2
 20 | Cat 1.2.1 |        17 |        1
  7 | Cat 2     |           |        2
 16 | Cat 2.1   |         7 |        1
  5 | Cat 3     |           |        3

So sort main entries (without parent_id) using order_by column and sort in children using order_by column for children in one level.

Upvotes: 2

Views: 76

Answers (1)

user330315
user330315

Reputation:

Note: I assume that the for id = 16 the parent_id should be 7, not 8

You need a recursive query to go through the whole tree. You need a way to "remember" the main sort order and then sort by two different criteria: one for the "overall" sort order and one for each child level:

with recursive tree as (
   select id, name, parent_id, order_by as main_order, null::int as child_order
   from category
   where parent_id is null
   union all
   select c.id, c.name, c.parent_id, p.main_order, c.order_by as child_order
   from category c
     join tree p on p.id = c.parent_id
)
select *
from tree 
order by main_order, child_order nulls first;

By carrying the order_by from the root level to all children, we can keep all rows that belong to the same root together. The rows for one root are then sorted according to the fake child_order - the root rows will have null for that column and the nulls first puts them at the beginning of each group.

Online example: http://rextester.com/ZVLII98217

Upvotes: 2

Related Questions