CODEWITHSUNDEEP
sqlsortingtree
fydelio
fydelio

Reputation: 953

SQL Tree Query - WITH RECURSIVE & UNION. How to sort by a second value

I have a category table with a tree like structure (see image below). For a fast single query, I have the following SQL to get the full tree.

WITH RECURSIVE search_tree(id, name, path, position) AS 
(
    SELECT id, name, ARRAY[id], position
    FROM table_name
    WHERE id = id

    UNION ALL

    SELECT table_name.id, table_name.name, path || table_name.id, 
           table_name.position
    FROM search_tree
    JOIN table_name ON table_name.parent_id = search_tree.id
    WHERE NOT table_name.id = ANY(path)
)
SELECT id, name, path, position
FROM search_tree 
ORDER BY path

This query result in the following table

id  | name         | path    | position
----+--------------+---------+-------------
  1 | Cat Pictures | {1}.    |.    0
  2 | Funny        | {1,2}.  |.    0
  3 | LOLCats      | {1,2,3} |.    1
  4 | Animated     | {1,2,4} |.    2
  5 | Classic      | {1,2,5} |.    0
  6 | Renaissance  | {1,6}   |.    1

So the ordering according to path works well. But what I require is to have the order according to the column position, if the path-level is the same level (like id 2 & 4, and 3, 4, 5).

Hence the order of ID would want is

ids: 1, 6, 2, 5, 3, 4

Epected data structure

How to I change my SQL statement to reflect that order?

Upvotes: 0

Views: 159

Answers (1)

Martin Samek
Martin Samek

Reputation: 173

It can be achieved this way https://www.db-fiddle.com/f/rpFiPjKSwHW88C4cp6o9Rm/0

with recursive search_tree(id, parentPath, name) as (
    select id, cast(row_number() over(order by pos) as text), name
    from objects
    where parent_id = 0
union all
    select o.id, concat(search_tree.parentPath, ',', cast(row_number() over(order by o.pos) as text)), o.name
    from search_tree
    join objects as o on search_tree.id = o.parent_id
)
select *
from search_tree
order by parentPath;

Upvotes: 1

Related Questions