Content Management and Capture

 View Only
  • 1.  Locating Compound Documents

    Posted Thu March 02, 2023 04:02 PM

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



  • 2.  RE: Locating Compound Documents

    Posted Thu March 02, 2023 05:53 PM

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



  • 3.  RE: Locating Compound Documents

    Posted Wed March 15, 2023 03:18 PM

    David - This is what we needed.  Thanks very much. 



    ------------------------------
    Joseph O'Toole
    ------------------------------