I would want, through recursive CTEs, a query to return all cars at tree-shaped, but sorted in each category

All we need is an easy explanation of the problem, so here it is.

Here is both table and data for testing:

CREATE TABLE if not exists cars (
    id serial PRIMARY KEY, 
    parent_code INT,
    descr CHARACTER VARYING(50) NOT NULL
);

-- Level 1
INSERT INTO cars(parent_code, descr) VALUES (null, 'Volkswagen');
INSERT INTO cars(parent_code, descr) VALUES (null, 'Tesla');

-- Level 2
INSERT INTO cars(parent_code, descr) VALUES (1, 'Polo');
INSERT INTO cars(parent_code, descr) VALUES (1, 'Golf');

INSERT INTO cars(parent_code, descr) VALUES (2, '3');
INSERT INTO cars(parent_code, descr) VALUES (2, 'S');
INSERT INTO cars(parent_code, descr) VALUES (2, 'X');

-- Level 3
INSERT INTO cars(parent_code, descr) VALUES (3, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (3, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (4, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (4, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (5, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (6, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (7, 'electric');

And here is the view (which I tried) with a recursive CTE:

WITH RECURSIVE tree_cars AS (
  select descr
  from cars
  union all

  select concat('  -> ', descr)
  from cars
)
--table tree_cars;
select * from tree_cars;

But the output is not what I expect:

Volkswagen
Tesla
Polo
Golf
3
S
X
diesel
gasoline
diesel
gasoline
electric
electric
electric
  -> Volkswagen
  -> Tesla
  -> Polo
  -> Golf
  -> 3
  -> S
  -> X
  -> diesel
  -> gasoline
  -> diesel
  -> gasoline
  -> electric
  -> electric
  -> electric

Since I would want something like:

Tesla
  -> 3
      -> electric
  -> S
      -> electric
  -> X
      -> electric
Volkswagen
  -> Golf
      -> diesel
      -> gasoline
  -> Polo
      -> diesel
      -> gasoline

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

With Recursive CTE you can use below query to achieve your output:

with recursive cte as (
   select id, descr, parent_code, 1 as lvl_, array[id] as path_
   from cars 
   where parent_code is null
   union all
   select t1.id, lpad('->', t2.lvl_ * 4) || ' ' ||t1.descr, t1.parent_code, t2.lvl_ + 1, t2.path_||t1.id
   from cars t1
     join cte t2 on t1.parent_code = t2.id
)
select descr from cte
order by path_

DEMO

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply