Лучший метод для поиска иерархических данных
Я смотрю на строение сооружения, которое позволяет запрашивать для данных с иерархической фильтрацией. У меня есть несколько идей, как я собираюсь пойти об этом, но задавался вопросом, существуют ли какие-либо рекомендации или предложения, которые могли бы быть более эффективными.
Как пример предполагают, что пользователь ищет задание. Области задания были бы следующие.
1: Scotland
2: --- West Central
3: ------ Glasgow
4: ------ Etc
5: --- North East
6: ------ Ayrshire
7: ------ Etc
Пользователь может искать конкретный (т.е. Глазго) или в большей области (т.е. Шотландия).
Два подхода, которые я рассматриваю:
- сохраните примечание детей в базе данных для каждой записи (т.е. кошка 1 имела бы 2, 3, 4 в ее дочернем поле), и запрос против той записи с a
SELECT * FROM Jobs WHERE Category IN Areas.childrenField. - Используйте рекурсивную функцию для нахождения всех результатов, у кого есть отношение к выбранной области.
Проблемы, которые я вижу от обоих:
- Содержание этих данных в дб будет означать иметь необходимость отслеживать все изменения в структуре.
- Рекурсия является медленной и inefficent.
Какие-либо идеи, предложение или рекомендации на лучшем подходе? Я использую ASP.NET C# с 2 005 дБ MSSQL.
5 ответов
Вот подход, который я видел:
Создайте поле varchar(max) под названием hierarchyid. Сгенерируйте базовые идентификаторы для всех корневых объектов. Для каждого дочернего объекта создайте идентификатор и добавьте к нему идентификатор родителя (родителей).
Пример таблицы
ID(PK) HierarchyID Area
1 sl Scotland
2 slwc West Central
3 slwcgg Glasgow
Пример запроса
SELECT * FROM Areas Where HierarchyID LIKE 'sl%'
Вы можете использовать Common Table Expressions для выполнения рекурсивных запросов. Я считаю эту технику очень мощной, легко читаемой и простой в обслуживании.
Как насчет этого?
Таблица =>
Id ParentId Name
Хорошая простая таблица ?
Тогда как насчет какой-нибудь красивой сложной части SQL для этого? (CTE, я думаю, потрясающе)
public object FetchCategoryTree()
{
var sql = @"SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
WITH AreaTree (ID, Name, ParentID, OrgLevel, SortKey) AS
(
-- Create the anchor query. This establishes the starting
-- point
SELECT
a.ID,
cast('---- ' + a.Name as varchar(255)),
a.ParentID,
cast('----' as varchar(55)),
CAST(a.ID AS VARBINARY(900))
FROM dbo.Area a
WHERE a.ParentID is null
UNION ALL
-- Create the recursive query. This query will be executed
-- until it returns no more rows
SELECT
a.ID,
cast('----' + b.OrgLevel + ' ' + a.Name as varchar(255)),
a.ParentID,
cast(b.OrgLevel+ '----' as varchar(55)),
CAST(b.SortKey + CAST (a.ID AS BINARY(4)) AS VARBINARY(900))
FROM dbo.Area a
INNER JOIN AreaTree b ON a.ParentID = b.ID
)
SELECT * FROM AreaTree
ORDER BY SortKey";
return FetchObject(sql);
}
Теперь это творит некоторую магию SQL, в которой я не уверен. Однако с точки зрения непрофессионала он в основном принимает первую часть в качестве корневого запроса. Затем он возвращается к таблице и выполняет вторую часть, используя ответ первой части через соединение, и продолжает делать, пока не может найти больше совпадений, в основном большой цикл. Это тоже довольно быстро.
Вы получите кучу строк с прикрепленным ключом сортировки. Упорядочив запрос по ключу сортировки, вы получите ответ типа:
---- parent 1
-------- child 1
-------- child 2
------------ child 2.1
---- parent 2
-------- etc
Может быть, это то, что вы ищете?
Я использую древовидную модель Джо Селко для иерархии налогов с продаж (штат / округ / город / разное) в нашем приложении, и она хорошо работает.
Ваш запрос «Найдите вакансии в этой области или ниже» будет выглядеть примерно так:
SELECT * FROM Jobs WHERE Jobs.AreaID IN
(SELECT P1.AreaID
FROM Areas AS P1, Areas AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.Areas.AreaID = @selectedAreaID)
Вы должны использовать вложенные наборы. Вот реализация в MySQL. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/