In-order sort for trees with user-defined ordering of tied vertices

usertreesort.sql

WITH RECURSIVE
categories_rel (id, parent_id, "order") AS (VALUES
  (1, NULL, 20), (2, NULL, 10),
  (99, 1, 6), (12, 1, 1), (23, 2, 1),
  (119, 99, 1), (121, 12, 1),
  (1193, 119, 1), (1193, 1, 5), (7, 1, 2), (6, 1, 3)
),
tree (id, parent_id, r, path) AS (
  SELECT id, parent_id, 0 AS r, ARRAY["order"], "order"
  FROM categories_rel
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, r + 1, tree.path || c."order", c."order"
  FROM tree
  JOIN categories_rel c ON c.parent_id = tree.id
)
SELECT repeat(' ', tree.r) || tree.id, tree.*
FROM tree
ORDER BY tree.path;
┌──────────┬──────┬───────────┬───┬────────────┬───────┐
│ ?column? │  id  │ parent_id │ r │    path    │ order │
├──────────┼──────┼───────────┼───┼────────────┼───────┤
│ 2        │    2 │           │ 0 │ {10}       │    10 │
│  23      │   23 │         2 │ 1 │ {10,1}     │     1 │
│ 1        │    1 │           │ 0 │ {20}       │    20 │
│  12      │   12 │         1 │ 1 │ {20,1}     │     1 │
│   121    │  121 │        12 │ 2 │ {20,1,1}   │     1 │
│  7       │    7 │         1 │ 1 │ {20,2}     │     2 │
│  6       │    6 │         1 │ 1 │ {20,3}     │     3 │
│  1193    │ 1193 │         1 │ 1 │ {20,5}     │     5 │
│  99      │   99 │         1 │ 1 │ {20,6}     │     6 │
│   119    │  119 │        99 │ 2 │ {20,6,1}   │     1 │
│    1193  │ 1193 │       119 │ 3 │ {20,6,1,1} │     1 │
└──────────┴──────┴───────────┴───┴────────────┴───────┘
(11 rows)