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
------------------------------

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 - This is what we needed.  Thanks very much.