Как я могу выбрать все листовые узлы в иерархии SQL под данным узлом?
У меня есть набор данных, который моделирует иерархию категорий. Корневая категория содержит набор категорий верхнего уровня. Каждая категория верхнего уровня содержит набор подкатегорий.
Каждая подкатегория имеет набор организаций. Данная организация может входить в несколько подкатегорий.
Конечные узлы этой иерархии - это организации. Организация потенциально может входить в несколько подкатегорий.
Данные хранятся в трех таблицах SQL:
organizations
organization_id organization_name
1 Org A
2 Org B
3 Org C
4 Org D
5 Org E
6 Org F
categories
category_id parent_id category_name
0 NULL Top Level Category
1 0 First Category
2 0 Second Category
3 1 Sub Category A
4 1 Sub Category B
5 1 Sub Category C
6 2 Sub Category D
organizations_categories -- Maps organizations to sub_categories
organization_id category_id
1 3
2 3
2 6
3 4
4 4
5 4
6 5
6 4
7 6
8 6
Я хотел бы иметь возможность выбрать список всех уникальных организаций в данной категории или подкатегории.
То, как я делаю это прямо сейчас, включает в себя сначала выяснение, какие подкатегории были запрошены, а затем цикл по каждой подкатегории в коде и выполнение select, чтобы все организации были сопоставлены с этой категорией. Результаты каждого выбора добавляются в массив. Этот массив содержит дубликаты, когда организация появляется в нескольких подкатегориях.
Я хотел бы заменить этот кладж запросом, который может эффективно выбирать список отдельных организаций по идентификатору одной из категорий в иерархии.
Я разрабатываю это решение с использованием PHP и MySQL.
Спасибо за ваше время и предложения.
Ответы 2
Предполагая, что ваша иерархия всегда ровно на 3 уровня:
SELECT DISTINCT
O.organization_id,
O.organization_name
FROM
Categories CAT
INNER JOIN Categories SUB ON
SUB.parent_id = CAT.category_id
INNER JOIN Category_Organizations CO ON
CO.category_id = SUB.category_id
INNER JOIN Organizations O ON
O.organization_id = CO.organization_id
WHERE
CAT.category_id = @category_id
Вы можете изменить это на один уровень, чтобы передать идентификатор подкатегории. Если в то время вы не знаете, есть ли у вас идентификатор категории или идентификатор подкатегории, вы можете сделать следующее:
SELECT DISTINCT
O.organization_id,
O.organization_name
FROM
Categories CAT
LEFT OUTER JOIN Categories SUB ON
SUB.parent_id = CAT.category_id
INNER JOIN Category_Organizations CO ON
CO.category_id IN (CAT.category_id, SUB.category_id)
INNER JOIN Organizations O ON
O.organization_id = CO.organization_id
WHERE
CAT.category_id = @category_id
Если ваша иерархия может иметь неизвестное количество уровней (или вы думаете, что это может произойти в будущем), попробуйте Деревья и иерархии Джо Селко в SQL для умных людей, чтобы узнать об альтернативных способах моделирования иерархии. В любом случае, это, наверное, хорошая идея.
Не уверен, что ваша модель данных позволяет это, но вы можете использовать один столбец индекса и двоичное дерево, чтобы легко хранить эту информацию в одной таблице OrganizationTree. Также есть то преимущество, что вы используете один запрос без каких-либо изменений для поиска на уровне категории, подкатегории или организации (например, дайте мне все результаты подкатегории X)
Надеюсь это поможет.
Адам.