Я работаю над базой данных активов, которая имеет иерархию. Кроме того, есть таблица «ReferenceAsset», которая эффективно указывает на актив. Референсный актив в основном функционирует как переопределение, но он выбирается так, как если бы он был уникальным новым активом. Одним из устанавливаемых переопределений является parent_id.
Столбцы, относящиеся к выбору иерархии:
Актив: id (основной), parent_id
Ссылка на ресурс: id (первичный), assets_id (внешний ключ->Актив), parent_id (всегда Актив)
-- -ОТРЕДАКТИРОВАНО 27 мая ----
Пример данных соответствующей таблицы (после соединений):
id | asset_id | name | parent_id | milestone | type 3 3 suit null march shape 4 4 suit_banker 3 april texture 5 5 tie null march shape 6 6 tie_red 5 march texture 7 7 tie_diamond 5 june texture -5 6 tie_red 4 march texture
id ‹ 0 (как в последней строке) означает активы, на которые есть ссылки. Ресурсы, на которые ссылаются, имеют несколько переопределенных столбцов (в данном случае важен только parent_id).
Ожидается, что если я выберу все активы за апрель, я должен сделать вторичный выбор, чтобы получить все ветви дерева соответствующего запроса:
поэтому изначально совпадение запроса приведет к:
4 4 suit_banker 3 april texture
Затем после CTE мы получаем полную иерархию, и наш результат должен быть таким (пока это работает)
3 3 suit null march shape 4 4 suit_banker 3 april texture -5 6 tie_red 4 march texture
и вы видите, родитель id:-5 есть, но чего не хватает, что необходимо, так это ссылочного актива и родителя ссылочного актива:
5 5 tie null march shape 6 6 tie_red 5 march texture
В настоящее время мое решение работает для этого, но оно ограничено только одной глубиной ссылок (и я чувствую, что реализация довольно уродлива).
---Отредактировано---- Вот моя основная функция выбора. Это должно лучше продемонстрировать, в чем заключается настоящая сложность: в файле AssetReference.
Select A.id as id, A.id as asset_id, A.name,A.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName,
L.name as locationName,AO.owner_name as ownerName, T.id as typeID,
M.name as milestoneName, A.deleted as bDeleted, 0 as reference, W.phase_name, W.status_name
FROM Asset as A Inner Join Type as T on A.type_id = T.id
Inner Join Batch as B on A.batch_id = B.id
Left Join Location L on A.location_id = L.id
Left Join Asset A2 on A.parent_id = A2.id
Left Join AssetOwner AO on A.owner_id = AO.owner_id
Left Join Milestone M on A.milestone_id = M.milestone_id
Left Join Workflow as W on W.asset_id = A.id
where A.deleted <= @showDeleted
UNION
Select -1*AR.id as id, AR.asset_id as asset_id, A.name, AR.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName,
L.name as locationName,AO.owner_name as ownerName, T.id as typeID,
M.name as milestoneName, A.deleted as bDeleted, 1 as reference, NULL as phase_name, NULL as status_name
FROM Asset as A Inner Join Type as T on A.type_id = T.id
Inner Join Batch as B on A.batch_id = B.id
Left Join Location L on A.location_id = L.id
Left Join Asset A2 on AR.parent_id = A2.id
Left Join AssetOwner AO on A.owner_id = AO.owner_id
Left Join Milestone M on A.milestone_id = M.milestone_id
Inner Join AssetReference AR on AR.asset_id = A.id
where A.deleted <= @showDeleted
У меня есть хранимая процедура, которая берет временную таблицу (#temp) и находит все элементы иерархии. Стратегия, которую я использовал, была следующей:
- Выберите всю системную иерархию во временную таблицу (#treeIDs), представленную списком всех ветвей дерева, разделенных запятыми.
- Получить всю иерархию запросов соответствия активов (из #temp)
- Получить все эталонные активы, на которые указывает Assets из иерархии
- Разобрать иерархию всех эталонных активов
На данный момент это работает, потому что ссылочные активы всегда являются последним элементом в ветке, но если бы это было не так, я думаю, у меня были бы проблемы. Я чувствую, что мне нужна лучшая форма рекурсии.
Вот мой текущий код, который работает, но я не горжусь им, и я знаю, что он ненадежен (потому что он работает, только если ссылки находятся внизу):
Шаг 1. Постройте всю иерархию
;WITH Recursive_CTE AS (
SELECT Cast(id as varchar(100)) as Hierarchy, parent_id, id
FROM #assetIDs
Where parent_id is Null
UNION ALL
SELECT
CAST(parent.Hierarchy + ',' + CAST(t.id as varchar(100)) as varchar(100)) as Hierarchy, t.parent_id, t.id
FROM Recursive_CTE parent
INNER JOIN #assetIDs t ON t.parent_id = parent.id
)
Select Distinct h.id, Hierarchy as idList into #treeIDs
FROM ( Select Hierarchy, id FROM Recursive_CTE ) parent
CROSS APPLY dbo.SplitIDs(Hierarchy) as h
Шаг 2. Выберите ветки всех активов, которые соответствуют запросу
Select DISTINCT L.id into #RelativeIDs FROM #treeIDs
CROSS APPLY dbo.SplitIDs(idList) as L
WHERE #treeIDs.id in (Select id FROM #temp)
Шаг 3. Получите все эталонные активы в ветвях (эталонные активы имеют отрицательные значения идентификатора, поэтому часть идентификатора ‹ 0)
Select asset_id INTO #REFLinks FROM #AllAssets WHERE id in
(Select #AllAssets.asset_id FROM #AllAssets Inner Join #RelativeIDs
on #AllAssets.id = #RelativeIDs.id Where #RelativeIDs.id < 0)
Шаг 4. Получите ветки всего, что было найдено на шаге 3
Select DISTINCT L.id into #extraRelativeIDs FROM #treeIDs
CROSS APPLY dbo.SplitIDs(idList) as L
WHERE
exists (Select #REFLinks.asset_id FROM #REFLinks WHERE #REFLinks.asset_id = #treeIDs.id)
and Not Exists (select id FROM #RelativeIDs Where id = #treeIDs.id)
Я попытался просто показать соответствующий код. Я очень благодарен всем, кто может помочь мне найти лучшее решение!
