Время от времени приходится общаться с СУБД FireBird и Interbase. И вот недавно возникла необходимость поиска и выбора всех вложенных элементов дерева. В моём случае необходимо было удалить все вложенные группы дерева при удалении родительской группы и в дальнейшем организовать поиск всех вложенных элементов.

Удаление можно сделать на триггерах, а вот с поиском так не прокатит. Поэтому используем рекурсивную процедуру выборки. (заранее оговорюсь, рекурсия — это вариант для небольших и не очень нагруженных БД, если база большая и нагрузка на СУБД планируется существенная, то рекурсии лучше избежать, пример того как это можно сделать будет ниже)

Итак, мы имеем таблицу с тремя полями (идентификатор, идентификатор родителя, наименование):

CREATE TABLE TEST_TABLE (
    ID         INTEGER NOT NULL,
    ID_PARENT  INTEGER,
    NAME       VARCHAR(250)
);

Для такой таблицы процедура рекурсивного удаления элемента со всеми вложенными элементами будет выглядеть так:

CREATE PROCEDURE DELETE_NODES (
    ID_NODE INTEGER)
AS
DECLARE VARIABLE ID_KID INTEGER;
begin
  /* Выбираем вложения */
  for select ID from test_table where ID_PARENT = :ID_NODE INTO :ID_KID
    do begin
        /* Запускаем процедуру для всех вложенных групп поочереди (рекурсия) */
        execute procedure delete_nodes(id_kid);
    end
    /* Удаляем группу*/
    delete from test_table where ID = :ID_NODE;

  suspend;
end

Вот и всё, вместо удаления можем делать выборку или изменение данных, всё что угодно.

Однако данный метод выборки не очень удобен в случае очень больших или очень нагруженных БД поскольку слишком перегружает работой сервер БД, в таком случае для выборки я использую одну хитрость. К таблице добавляем строковое поле PATH (путь) в котом будем хранить полный путь элемента (идентификаторы элементов от корневого до текущего через точку), при этом у родительских элементов всегда будет совпадать начало строки но у каждого элемента строка будет уникальная. После чего делаем поиск всех вложений не рекурсией а обычной выборкой с параметром.

Наглядно это будет выглядеть примерно так:

1   0   1.       Основная группа
2   1   1.2.     Вторая группа
3   1   1.3.     Третья группа
4   2   1.2.4.   Вложение второй группы
5   2   1.2.5.   Второе вложение второй группы
6   3   1.3.6.   Подгруппа для группы номер с ID=3

Таким образом сделав выборку для группы номер 2 таким образом:

select * from TEST_TABLE where PATH like '1.2.%'

Мы получим выборку всех потомков этого элемента включая его самого.

Вот так.