PostgreSQL - древовидная организация

Смотрите на "ltree" contrib модуль.

Существует acts_as_tree плагин для направляющих, который работал хорошо на меня в прошлом. У меня было довольно маленькое дерево, хотя - приблизительно 15 000 узлов.

Я играл вокруг с ltree, который является модулем PostgreSQL contrib, чтобы видеть, является ли он подходящим вариантом для потоковых комментариев. Вы создаете столбец в своей таблице, которая хранит путь, и создайте индекс ltree на нем.. Можно затем выполнить запросы как это:

 ltreetest=# select path from test where path ~ '*.Astronomy.*';
                     path                      
-----------------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Collections.Pictures.Astronomy
 Top.Collections.Pictures.Astronomy.Stars
 Top.Collections.Pictures.Astronomy.Galaxies
 Top.Collections.Pictures.Astronomy.Astronauts

Я не играл вокруг с ним достаточно, чтобы определить, как хорошо это работает с вещами, любят, вставляет, обновляет или удаляет. Я предполагаю, что удаление было бы похоже:

DELETE FROM test WHERE path ~ '*.Astronomy.*';

Я думаю, потоковая таблица комментария могла бы быть похожей:

CREATE SEQUENCE comment_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 78616
  CACHE 1;

CREATE TABLE comments (
comment_id int PRIMARY KEY,
path ltree,
comment text
);

CREATE INDEX comments_path_idx ON comments USING gist (path);

Вставка была бы грубо (и непротестированный-ly) будьте похожи:

CREATE FUNCTION busted_add_comment(text the_comment, int parent_comment_id) RETURNS void AS
$BODY$
DECLARE
    INT _new_comment_id; -- our new comment_id
    TEXT _parent_path;   -- the parent path
BEGIN
    _new_comment_id := nextval('comment_id_seq'::regclass);
    SELECT path INTO _parent_path FROM comments WHERE comment_id = parent_comment_id;

    -- this is probably busted SQL, but you get the idea... this comment's path looks like
    --   the.parent.path.US
    --
    -- eg (if parent_comment_id was 5 and our new comment_id is 43):
    --  3.5.43
    INSERT INTO comments (comment_id, comment, path) VALUES (_new_comment_id, the_comment, CONCAT(_parent_path, '.', _new_comment_id));

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Или что-то. В основном путь является просто иерархией, составленной из всех первичных ключей.

Я стал любящим вложенная модель набора для этого вида ситуации. Обновления и Вставки могут быть немного хитрыми, но выборы обычно очень кратки и быстры. Производительность может быть еще лучше, если Вы добавите фактическую ссылку на родителя узла (то это устранит соединение в некоторых случаях. Это также включает естественную сортировку childnodes.

• Деревья в SQL

Типичный запрос для текущего узла и всех детей был бы похож:

select name
from nestedSet c inner join nestedSet p ON c.lft BETWEEN p.lft AND p.rgt
where p.id = 1
order by lft

Некоторые хорошо размещенные group by пункты будут также сеть Вы некоторая быстрая статистика о Вашем дереве.

Только для добавления статья Managing Hierarchical Data в MySQL имеет хорошее объяснение Модели Списка Смежности и Вложенных Моделей Набора, включая пример SQLs для древовидного управления и такого.

Иерархии в RDBMS являются трудной темой. У меня есть Деревья Joe Celko и Иерархии в SQL для Присяжных острословов в моем списке пожеланий, чтобы купить и читать однажды.