Как сделать сортировку рекурсивного запроса в postgresql?

Question

[Руслан]

Здравствуйте, подскажите как правильно сделать что бы сортировка работала корректно:
есть таблица
id, pid, sort, name где pid это foreign key на id нужно одним запросом вытянуть все дерево и отсортировать по вложенности и по полю sort

делаю запрос:

WITH RECURSIVE rec_category AS (
    (SELECT 1 AS depth, ARRAY[id] AS path, * FROM taa_periodical_category WHERE  pid = 0)
        UNION ALL
            SELECT r.depth + 1, r.path, n.*
            FROM   rec_category r 
            JOIN   taa_periodical_category n ON n.pid = r.id
    )
SELECT id, pid, depth, path, sort, name FROM rec_category ORDER BY path, sort;

получаю:

id | pid | depth |  path        | sort | name
11 |   0 |     1 |         {11} |    2 | 'Категория 1'
13 |  11 |     2 |     {11}{11} |    4 | 'Категория 1 - 1'
16 |  11 |     2 |     {11}{11} |    7 | 'Категория 1 - 2'
18 |  11 |     2 |     {11}{11} |    8 | 'Категория 1 - 3'
17 |  11 |     2 |     {11}{11} |    9 | 'Категория 1 - 4'
19 |  18 |     3 | {11}{11}{18} |   10 | 'Категория 1 - 3 - 2'
20 |  18 |     3 | {11}{11}{18} |   11 | 'Категория 1 - 3 - 1'
12 |   0 |     1 |         {12} |    3 | 'Категория 2'
14 |  12 |     2 |     {12}{12} |    5 | 'Категория 2 - 1'
15 |  12 |     2 |     {12}{12} |    6 | 'Категория 2 - 2'

как видно Категория 1 - 3 - 2 и Категория 1 - 3 - 1 идут после Категория 1 - 4, а должны идти после Категория 1 - 3

Answer 1

[Melkij]

Так сравниваются массивы - поэлементно по количеству элементов в меньшем из массивов.
Если элементы идентичны - сравниваем длины массивов. Поэтому массивы в таком виде вам подходят слабо.

Может лучше ваш список смежности вовсе заменить на штатное материализованное представление ltree?
Ну или во всяком случае на него можно заменить ваши массивы.

Comments

[Руслан]

думаю что действительно это будет более правильным вариантом