Original Message:
Sent: Thu March 02, 2023 05:53 PM
From: David Alfredson
Subject: Locating Compound Documents
Hi Joseph,
You can use a recursive query. This SQL is for SQL Server. You can customise the columns you get back from the query to display the path or do other functions on the data. This query retrieves all the compound relationships in the DB, displays the child and parent component ID, shows the depth of the relationship where 0 is the first one, shows the path using the relationship object ids.
WITH cte_org AS (
SELECT
object_id,
parent_component_id,
child_component_id,
cast(convert(nvarchar(36), object_id) as text) as cdpath,
0 as depth
FROM
ComponentRelation
UNION ALL
SELECT
e.object_id,
e.parent_component_id,
e.child_component_id ,
cast(concat(o.cdpath,'->', convert(nvarchar(36),e.object_id )) as text) as cdpath,
o.depth + 1
FROM
ComponentRelation e
INNER JOIN cte_org o
ON o.child_component_id = e.parent_component_id
)
SELECT * FROM cte_org order by parent_component_id, child_component_id;
------------------------------
David Alfredson
Original Message:
Sent: Thu March 02, 2023 01:17 PM
From: Joseph O'Toole
Subject: Locating Compound Documents
We are attempting to identify all child documents that are part of a compound document scenario. Is there a way to determine child documents no matter how nested they may be?
We used something like this to determine child documents one level down -
SELECT * FROM P8PROVIDER.DOCVERSION WHERE OBJECT_ID IN(
SELECT CR.CHILD_COMPONENT_ID FROM P8PROVIDER.COMPONENTRELATION CR WHERE CR.PARENT_COMPONENT_ID IN
(SELECT DV.OBJECT_ID
FROM P8PROVIDER.DOCVERSION DV INNER JOIN P8PROVIDER.CLASSDEFINITION CD
ON DV.OBJECT_CLASS_ID = CD.OBJECT_ID WHERE CD.SYMBOLIC_NAME = 'PROVIDERFOLDER' AND DV.COMPOUND_DOCUMENT_STATE = 1))
In order to determine if there are children two levels down -
SELECT SUM(CONTENT_SIZE) FROM P8PROVIDER.DOCVERSION WHERE OBJECT_ID IN(
SELECT CRB.CHILD_COMPONENT_ID FROM P8PROVIDER.COMPONENTRELATION CRB WHERE CRB.PARENT_COMPONENT_ID IN (
SELECT CR.CHILD_COMPONENT_ID FROM P8PROVIDER.COMPONENTRELATION CR WHERE CR.PARENT_COMPONENT_ID IN
(SELECT DV.OBJECT_ID
FROM P8PROVIDER.DOCVERSION DV INNER JOIN P8PROVIDER.CLASSDEFINITION CD
ON DV.OBJECT_CLASS_ID = CD.OBJECT_ID
WHERE CD.SYMBOLIC_NAME = 'PROVIDERFOLDER' AND DV.COMPOUND_DOCUMENT_STATE = '1')))
To check if there are further descendant children we can add another layer to the query. Is there a way to determine all of the children of compound documents without this type of iterative querying?
------------------------------
Joseph O'Toole
------------------------------