SQL: сохранить root_id в трехуровневом дереве?

У меня есть простое трехуровневое дерево, которое в настоящее время хранится в виде списка смежности:

CREATE TABLE categories (
  id int,
  name text,
  parent_id int);

Он доступен только для чтения, и мне часто нужно знать корневую категорию любой данной категории. Поэтому я хочу добавить столбец root_id и сохранить его, чтобы избежать беспорядочных предложений WHERE, CTE и т. д.

Моя первая попытка была:

update categories set root_id = parent.id
from categories as child
join categories as parent
on child.parent_id = parent.id
and parent.parent_id is null;

Но UPDATE не может работать с объединениями, создающими несколько строк . (Этот запрос был только для категорий уровня 2; я бы сделал аналогичный запрос для третьего уровня.)

Я знаю, что ответ включает в себя превращение соединения в подзапрос, но я не могу думать о самообъединениях и подзапросах одновременно. Мы используем PostgreSQL 9.0, поэтому у меня нет доступных для записи CTE (хотя мне любопытно, как это будет выглядеть).

Как правильно это сделать?


postgresql sql adjacency-list self-join
person Jay Levitt    schedule 10.11.2011    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вам не нужно хранить корень в каждой записи, вы можете легко найти его с помощью рекурсивного запроса.

Небольшая демонстрация погони за дедушкой. (здесь соглашение состоит в том, что любой корень имеет parent_id = NULL; другим соглашением может быть parent_id=id; YMMV)

set search_path='tmp';

DROP TABLE categories;
CREATE TABLE categories
  ( id INTEGER NOT NULL PRIMARY KEY
  , parent_id INTEGER references categories(id)
  , zname text
  );

-- Generate some data
INSERT INTO categories (id) SELECT * FROM generate_series (1,33);
UPDATE categories SET parent_id = 1 + id/4 WHERE id > 1;
-- uncomment the next line (and comment the previous) if you want a forest.
-- UPDATE categories SET parent_id = 1 + id/4 WHERE id > 3;
UPDATE categories SET zname = 'Zzz'::text || id::text;


-- SELECT * FROM categories;

WITH RECURSIVE tr AS (
    SELECT ca0.id as root
    , ca0.parent_id AS parent_id
    , ca0.id AS id
    , ca0.zname AS zname
    FROM categories ca0
    WHERE ca0.parent_id IS NULL
    UNION
    SELECT tr.root as root
    , ca1.parent_id AS parent_id
    , ca1.id AS id
    , ca1.zname AS zname
    FROM categories ca1, tr
    WHERE ca1.parent_id = tr.id
    )
SELECT * FROM tr;

Результат:

 root | parent_id | id | zname
------+-----------+----+-------
    1 |           |  1 | Zzz1
    1 |         1 |  2 | Zzz2
    1 |         1 |  3 | Zzz3
    1 |         2 |  4 | Zzz4
    1 |         2 |  5 | Zzz5
    1 |         2 |  6 | Zzz6
    1 |         2 |  7 | Zzz7
    1 |         3 |  8 | Zzz8
    1 |         3 |  9 | Zzz9
    1 |         3 | 10 | Zzz10
    1 |         3 | 11 | Zzz11
    1 |         4 | 12 | Zzz12
    1 |         4 | 13 | Zzz13
    1 |         4 | 14 | Zzz14
    1 |         4 | 15 | Zzz15
    1 |         5 | 16 | Zzz16
    1 |         5 | 17 | Zzz17
    1 |         5 | 18 | Zzz18
    1 |         5 | 19 | Zzz19
    1 |         6 | 20 | Zzz20
    1 |         6 | 21 | Zzz21
    1 |         6 | 22 | Zzz22
    1 |         6 | 23 | Zzz23
    1 |         7 | 24 | Zzz24
    1 |         7 | 25 | Zzz25
    1 |         7 | 26 | Zzz26
    1 |         7 | 27 | Zzz27
    1 |         8 | 28 | Zzz28
    1 |         8 | 29 | Zzz29
    1 |         8 | 30 | Zzz30
    1 |         8 | 31 | Zzz31
    1 |         9 | 32 | Zzz32
    1 |         9 | 33 | Zzz33
(33 rows)
person wildplasser    schedule 10.11.2011
comment
Я думаю, что это идеально, но он прямо сказал, что не хочет или не может использовать CTE. - person Ben English; 10.11.2011
comment
Что ж, мне не нужен CTE для реального запроса (Rails/ActiveRecord понятия не имеет, как их использовать), но я не против использовать его для обновления, и это работает отлично — отредактирую ответ. - person Jay Levitt; 10.11.2011
comment
Если вам нужно только rvalue (CTE), вы можете использовать рекурсивный запрос в самой правой части запроса UPDATE (или DELETE, или EXISTS). Вы можете построить представление вокруг него, создав прародителя данного идентификатора. Только чтобы доказать, что CTE не обязательно беспорядочны... - person wildplasser; 10.11.2011
comment
Да, это именно то, что я сделал (используйте его в самой правой части ОБНОВЛЕНИЯ). Редактирование ожидает рассмотрения, так как у меня пока нет прав... спасибо, что научили меня CTE. - person Jay Levitt; 11.11.2011