Как проверить, существуют ли отношения родитель/потомок в дереве?

Было бы полезно проверить, имеют ли следующие таблицы определенную связь между своими записями:

-- Table: privilege_group
CREATE TABLE privilege_group (
    privilege_group_id integer NOT NULL CONSTRAINT privilege_group_pk PRIMARY KEY AUTOINCREMENT,
    name text NOT NULL,
    CONSTRAINT privilege_group_name UNIQUE (name)
);

-- Table: privilege_relationship
CREATE TABLE privilege_relationship (
    privilege_relationship_id integer NOT NULL CONSTRAINT privilege_relationship_pk PRIMARY KEY AUTOINCREMENT,
    parent_id integer NOT NULL,
    child_id integer NOT NULL,
    CONSTRAINT privilege_relationship_parent_child UNIQUE (parent_id, child_id),
    CONSTRAINT privilege_relationship_parent_id FOREIGN KEY (parent_id)
    REFERENCES privilege_group (privilege_group_id),
    CONSTRAINT privilege_relationship_child_id FOREIGN KEY (child_id)
    REFERENCES privilege_group (privilege_group_id),
    CONSTRAINT privilege_relationship_check CHECK (parent_id != child_id)
);

У родителей может быть много детей, у детей может быть много родителей. Написание кода для обработки записей вне базы данных всегда возможно, но можно ли использовать поиск в глубину (или в ширину), чтобы проверить, есть ли у дочернего элемента определенный родитель?

Мой связанный вопрос получил комментарий от CL . в котором упоминается предложение WITH, но мой опыт работы с иерархическими запросами довольно ограничен и недостаточен понять, выбрать и применить примеры на странице к моей цели:

  • Работал только с иерархическими запросами в Oracle.
  • Используется только для реализации генераторов чисел "диапазона" (например, в Python).
  • Видел только, как обрабатывать записи по шаблону от широкого к узкому.
  • Не уверен, что расширяющийся набор результатов в иерархическом запросе возможен.
  • Не знаете, как выбрать стратегию поиска в глубину или в ширину.

Может ли кто-нибудь показать мне, как узнать, есть ли у ребенка родитель, если известны имена обоих?


sqlite recursive-query
person Noctis Skytower    schedule 22.12.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это стандартный поиск по дереву (с использованием UNION вместо UNION ALL для предотвращения бесконечных циклов):

WITH RECURSIVE ParentsOfG1(id) AS (
  SELECT privilege_group_id
  FROM privilege_group
  WHERE name = 'G1'

  UNION

  SELECT parent_id
  FROM privilege_relationship
  JOIN ParentsOfG1 ON id = child_id
)
SELECT id
FROM ParentsOfG1
WHERE id = (SELECT privilege_group_id
            FROM privilege_group
            WHERE name = 'P2');

Глубина/ширина-сначала не имеет значения для этого.

person CL.    schedule 23.12.2017
comment
Спасибо за Ваш ответ! Я надеюсь протестировать ваше решение к концу недели. - person Noctis Skytower; 26.12.2017

arrow_upward
0
arrow_downward

Альтернативой ответу CL. может быть этот запрос, который был переформатирован и скорректирован для использования связанных параметров, которые можно было бы подключить в проект, который должен проверить определенные отношения:

WITH RECURSIVE parent_of_child(id)
            AS (
        SELECT privilege_group_id
          FROM privilege_group
         WHERE name = :child
         UNION
        SELECT parent_id
          FROM privilege_relationship
          JOIN parent_of_child
            ON id = child_id)
        SELECT id
          FROM parent_of_child
         WHERE id = (
        SELECT privilege_group_id
          FROM privilege_group
         WHERE name = :parent)
person Noctis Skytower    schedule 27.12.2017