Сортировка дерева с осуществленным путем?

Question

Сортировка дерева с осуществленным путем?

У меня есть древовидная структура в таблице, и она использует осуществленные пути, чтобы позволить мне находить детей быстро. Однако я также должен отсортировать результаты, в глубину, как можно было бы ожидать с потоковыми ответами форума.

 id | parent_id | matpath |          created           
----+-----------+---------+----------------------------
  2 |         1 | 1       | 2010-05-08 15:18:37.987544
  3 |         1 | 1       | 2010-05-08 17:38:14.125377
  4 |         1 | 1       | 2010-05-08 17:38:57.26743
  5 |         1 | 1       | 2010-05-08 17:43:28.211708
  7 |         1 | 1       | 2010-05-08 18:18:11.849735
  6 |         2 | 1.2     | 2010-05-08 17:50:43.288759
  9 |         5 | 1.5     | 2010-05-09 14:02:43.818646
  8 |         6 | 1.2.6   | 2010-05-09 14:01:17.632695

Таким образом, конечные результаты должны на самом деле быть отсортированы как это:

 id | parent_id | matpath |          created
----+-----------+---------+----------------------------
  2 |         1 | 1       | 2010-05-08 15:18:37.987544
  6 |         2 | 1.2     | 2010-05-08 17:50:43.288759
  8 |         6 | 1.2.6   | 2010-05-09 14:01:17.632695
  3 |         1 | 1       | 2010-05-08 17:38:14.125377
  4 |         1 | 1       | 2010-05-08 17:38:57.26743
  5 |         1 | 1       | 2010-05-08 17:43:28.211708
  9 |         5 | 1.5     | 2010-05-09 14:02:43.818646
  7 |         1 | 1       | 2010-05-08 18:18:11.849735

Как я разработал бы это? Я могу сделать это в прямом SQL (это - PostgreSQL 8.4), или дополнительная информация должна быть добавлена к этой таблице?

Обновление: попытка объяснить критерии сортировки лучше.

Предположите, что идентификатор '1' является корневым сообщением к форуму, и все с началом 'matpath' '1' является ребенком того сообщения. Таким образом, идентификаторы 2 - 5 являются прямыми ответами на 1 и получают matpaths '1'. Однако идентификатор 6 является ответом 2, не непосредственно к 1, таким образом, это получает matpath 1,2. Это означает, что для потокового форума с надлежащим вложением, со всеми идентификаторами, показанными в таблицах, структура форума была бы похожа на это, следовательно требование упорядочивания:

* id 1 (root post)
    * id 2
        * id 6
            * id 8
    * id 3
    * id 4
    * id 5
        * id 9
    * id 7

14

sql postgresql materialized-path-pattern

задан Ovid 9 May 2010 в 13:30

3 ответа

Я не могу придумать простого способа сделать это в прямом SQL. Вместо matpath я буду использовать здесь node_path. node_path - это matpath || '.' || id

 id | parent_id | node_path |          created           
----+-----------+---------+----------------------------
  2 |         1 | 1.2       | 2010-05-08 15:18:37.987544
  3 |         1 | 1.3       | 2010-05-08 17:38:14.125377
  4 |         1 | 1.4       | 2010-05-08 17:38:57.26743
  5 |         1 | 1.5       | 2010-05-08 17:43:28.211708
  7 |         1 | 1.7       | 2010-05-08 18:18:11.849735
  6 |         2 | 1.2.6     | 2010-05-08 17:50:43.288759
  9 |         5 | 1.5.9     | 2010-05-09 14:02:43.818646
  8 |         6 | 1.2.6.8   | 2010-05-09 14:01:17.632695

Теперь вы хотите упорядочить дерево на основе node_path, с полем сортировки, определяемым количеством раз, когда вы выполняли сортировку.

Пользовательская рекурсивная функция сортировки plpgsql по split_part (node_path, '.', Recursion_depth) будет работать. Вам нужно будет проверить значения NULL из split_part (и игнорировать их).

3

ответ дан 1 December 2019 в 07:19

Я считаю, что ваш материализованный путь неправильный.

Какая логика у вас есть для сортировки таких вещей

Почему вторая единица не совпадает с первой?

Если бы у вас была

Это было бы тривиально.

РЕДАКТИРОВАТЬ: Я рассмотрел ваш пример, и вы не сохраняете материализованный путь строки, но сохраняете материализованный путь родительской строки. Вот как материализованный путь строки строка на самом деле должна выглядеть так. Сортировка непосредственно по matpath будет работать, если у вас не будет более 9 ветвей, если вы сохраните ее как:

 id | parent_id | matpath   |          created
----+-----------+-----------+----------------------------
  2 |         1 | 1.2       | 2010-05-08 15:18:37.987544
  6 |         2 | 1.2.6     | 2010-05-08 17:50:43.288759
  8 |         6 | 1.2.6.8   | 2010-05-09 14:01:17.632695
  3 |         1 | 1.3       | 2010-05-08 17:38:14.125377
  4 |         1 | 1.4       | 2010-05-08 17:38:57.26743
  5 |         1 | 1.5       | 2010-05-08 17:43:28.211708
  9 |         5 | 1.5.9     | 2010-05-09 14:02:43.818646
  7 |         1 | 1.7       | 2010-05-08 18:18:11.849735

в противном случае (> 9) вам придется превратить matpath во что-то вроде

001.002.006
001.002.006.008

, которое будет поддержка до 999 филиалов.

Обратите внимание

, что даже подход с 4 фиксированными цифрами, например 0001.0002.0006 , даст вам поле, которое короче, чем в принятом ответе
, вы можете проанализировать matpath и произвести сортировку значение на лету с помощью пользовательской функции
вы можете напрямую сохранить matpath в этом формате (у него есть и другие приятные свойства)

14

ответ дан 1 December 2019 в 07:19

Другие вопросы по тегам:

sql postgresql materialized-path-pattern

Похожие вопросы:

score 8 · Accepted Answer

Обычно я создаю для этого дополнительный столбец, который называется что-то вроде SortPath. Он будет содержать данные, по которым нужно отсортировать, сгруппированные вместе. Этот столбец будет иметь тип varchar, и будет отсортирован как строка. Что-то вроде этого:

id | parent_id | matpath |          created            |                   sortpath
---+-----------+---------+-----------------------------+--------------------------------------------------------------------------------------
 2 |         1 | 1       | 2010-05-08 15:18:37.987544  | 2010-05-08 15:18:37.987544-2
 6 |         2 | 1.2     | 2010-05-08 17:50:43.288759  | 2010-05-08 15:18:37.987544-2.2010-05-08 17:50:43.288759-6
 8 |         6 | 1.2.6   | 2010-05-09 14:01:17.632695  | 2010-05-08 15:18:37.987544-2.2010-05-08 17:50:43.288759-6.2010-05-09 14:01:17.632695-8
 3 |         1 | 1       | 2010-05-08 17:38:14.125377  | 2010-05-08 17:38:14.125377-3
 4 |         1 | 1       | 2010-05-08 17:38:57.26743   | 2010-05-08 17:38:57.267430-4 
 5 |         1 | 1       | 2010-05-08 17:43:28.211708  | 2010-05-08 17:43:28.211708-5
 9 |         5 | 1.5     | 2010-05-09 14:02:43.818646  | 2010-05-08 17:43:28.211708-5.2010-05-09 14:02:43.818646-9
 7 |         1 | 1       | 2010-05-08 18:18:11.849735  | 2010-05-08 18:18:11.849735-7

Пара моментов, которые следует отметить:

sortpath будет отсортирован как строка, поэтому важно, чтобы все даты имели одинаковую длину для правильной сортировки. Например, обратите внимание, как 2010-05-08 17:38:57.26743 имеет дополнительный ноль в колонке sortpath.
Я добавил PK каждого узла в конец его даты. Это сделано для того, чтобы, если у вас есть две строки с одинаковой датой, они всегда возвращались в том же порядке из-за дополнительных данных, которые мы добавляем.
На мой взгляд, данные выглядят асимметрично, потому что мы показываем дату текущего узла в sortpath, но не в matpath. Я бы предпочел видеть ее в обоих.
Возможно, вы захотите поместить дату ID узла 1 в начало каждого sortcolumn. Это нужно для того, чтобы, если вы когда-нибудь захотите запросить более одного форума за раз (скорее всего, вы этого не сделаете), то сортировка все равно будет правильной.