# 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 🙂