CODEWITHSUNDEEP
postgresql
Sandeep
Sandeep

Reputation: 721

Query to make dynamic sorting in Postgres for child elements

I have a data set like below(PostgresQL version 11),I want to add a sorting column to this with decimal points so that i can easily map the parent - child tree in my visualisation.

Any method or query we can do that,Tried with row number and rank but that is not helping.

Data Set:

Parent      Child
Vitamine    Vitamine
Vitamin D   Vitamin D
Vitamin D   Vitamin D3
Vitamin D   Vitamin D K2
Vitamin D   D3
Vitamin D   Vitamin D 1000
Vitamin D   Vitamin D 10000
Vitamin D   Vitamin D 20000
Vitamin K2  Vitamin K2
Vitamin K2  Vitamin K
Vitamin K2  Vitamin K2 Mk7
Vitamin C   Vitamin C

Expected out put

  Parent      Child             Sort_order
    Vitamine    Vitamine           1
    Vitamin D   Vitamin D          2
    Vitamin D   Vitamin D3         2.1
    Vitamin D   Vitamin D K2       2.2
    Vitamin D   D3                 2.3
    Vitamin D   Vitamin D 1000     2.4
    Vitamin D   Vitamin D 10000    2.5
    Vitamin D   Vitamin D 20000    2.6
    Vitamin K2  Vitamin K2         3
    Vitamin K2  Vitamin K          3.1
    Vitamin K2  Vitamin K2 Mk7     3.2
    Vitamin C   Vitamin C          4

Upvotes: 0

Views: 210

Answers (1)

John Klakegg
John Klakegg

Reputation: 943

Considering the following table schema:

create table vitamins (
  id serial primary key,
  parent_id integer,
  name varchar(64),
  index smallint
);

with the following data:

insert into vitamins (id, parent_id, name, index) values
  (1, null, 'Vitamine', 1),
  (2, null, 'Vitamin D', 2),
  (3, 2, 'Vitamin D3', 1),
  (4, 2, 'Vitamin D K2', 2),
  (5, 2, 'D3', 3),
  (6, 2, 'Vitamin D 1000', 4),
  (7, 2, 'Vitamin D 10000', 5),
  (8, 2, 'Vitamin D 20000', 6),
  (9, null, 'Vitamin K2', 3),
  (10, 9, 'Vitamin K', 1),
  (11, 9, 'Vitamin K2 Mk7', 2),
  (12, null, 'Vitamin C', 4)
;

We can get the expected output you want with the following query:

WITH RECURSIVE recursive_vitamins(id, parent_id, index, parent_name, name, ref, sort) AS (
    SELECT c.id, c.parent_id, c.index, c.name, c.name, c.index::text, lpad(c.index::text, 3, '0')
    FROM vitamins c
    WHERE parent_id is null
    UNION ALL
    SELECT c.id, c.parent_id, c.index, rc.parent_name, c.name,
           CASE WHEN rc.ref = '' THEN c.index::text
                ELSE (rc.ref || '.' || c.index)
               END, (rc.sort || '.' || lpad(c.index::text, 3, '0'))
    FROM vitamins c, recursive_vitamins rc
    WHERE c.parent_id = rc.id
)

SELECT
    parent_name AS parent,
    name AS child,
    ref AS sort_order
FROM recursive_vitamins
ORDER BY sort;

Result:

parent      child         sort_order
Vitamine    Vitamine      1
Vitamin D   Vitamin D     2
Vitamin D   Vitamin D3    2.1
Vitamin D   Vitamin D K2  2.2
Vitamin D   D3            2.3
...

(sqlfiddle of the whole thing)

Hold up, what's happening here?

So, we create a recursive CTE allowing the query to refer to it's own output. In the WITH query we first select all parents (parent_id is null) then we select all childs that refer to the the parent_id.

The ref field is a concatenation of parent and child index. The sort field refers to index as well, but it left-pads the index with 0 to a total length of 3. You could leave this out and just order by ref, but if you have an index >= 10, the order would become 1, 10, 10.1, 2 which I would guess you don't want.

Upvotes: 1

Related Questions