How to get recursive folder path from Alfresco

How to get recursive folder path from Alfresco

Nathan Obert
Blog
How to get recursive folder path from Alfresco
Page content

Introduction

Alfresco is a Content Management System, recently purchased by Hyland. I needed to write a query for Oracle and Microsoft SQL that would loop through its database and build a folder structure recursivily from its tables. These snippets of SQL are what I came up with.

Microsoft SQL Server

This Article here is probably the best article I’ve found that explains MS SQL Recursion with a working example. https://www.andrewvillazon.com/recursive-cte-sql-server/

Alfresco MS SQL Recursion to get Folder Pathes

WITH ancestor AS
-- Base Query
(SELECT     
	a1.parent_node_id,
    a1.child_node_id as node_id,
    a1.qname_localname AS name,
	CAST(a1.qname_localname as VARCHAR(MAX)) as path
  FROM alf_child_assoc AS a1
  -- Anchor
  WHERE a1.qname_localname ='company_home'
  UNION ALL
-- Recursion
  SELECT     
	a2.parent_node_id,
    a2.child_node_id as node_id,	
    a2.qname_localname AS name,
	CAST(ancestor.path + '\' + a2.qname_localname as VARCHAR(MAX)) as path    	
  FROM ancestor, alf_child_assoc AS a2
  WHERE ancestor.node_id = a2.parent_node_id
)select * from ancestor

SQL Server when it does recursion only needs the child’s name in the parent-child relationship to make a tree structure. With Oracle however, it appears you need a parent and a child’s name to properly populate a recursive tree from the same table and rows.

Additionally on Oracle since I had to retrieve the parent’s name from Node Properties, I also pulled the child’s name from Node Properties for consistancy sakes.

Oracle Server

SELECT            
    parent_node_id,
    child_node_id,
    child_string_value name,
    sys_connect_by_path(parent_string_value, '\') || '\' || child_string_value AS path
        FROM
            (
                -- Base Query
                SELECT
                    alf_child_assoc.parent_node_id,
                    alf_child_assoc.child_node_id,
                    parent_node_properties.string_value parent_string_value,
                    child_node_properties.string_value  child_string_value
                FROM
                    alf_child_assoc,
                    alf_node_properties child_node_properties,
                    alf_node_properties parent_node_properties
                WHERE  

                    -- Child's name.
                        alf_child_assoc.child_node_id = child_node_properties.node_id
                    AND parent_node_properties.qname_id IN (
                        SELECT
                            id
                        FROM
                            alf_qname
                        WHERE
                            local_name = 'name'
                    )
                    AND
    
                    -- Parent's name
                     alf_child_assoc.parent_node_id = parent_node_properties.node_id
                    AND child_node_properties.qname_id IN (
                        SELECT
                            id
                        FROM
                            alf_qname
                        WHERE
                            local_name = 'name'
                    )                  
            ) folders

        -- Anchor
        START WITH
           
            parent_node_id = (
                SELECT
                    node_id
                FROM
                    alf_node_properties
                WHERE
                        string_value = 'Company Home'
                    AND qname_id IN (
                        SELECT
                            id
                        FROM
                            alf_qname
                        WHERE
                            local_name = 'name'
                    )
            )

        -- Recursion    
        CONNECT BY
            PRIOR child_node_id = parent_node_id

image