Recursive folder size

MohamedYousuff · 2024-03-11T10:04:38+00:00

There was an error rendering this rich post.

Need DQL to find the folder size recursively starting from a specific folder id.

Assume folder structure like below.  0b00138e806ab014 is r_object_id of the folder and number 100 refers to the document size (r_full_content_size) , not necessarily always 100 but just a sample number for simplicity. 


folder a

folder b - 0b00138e806bb023

    doc1 - 100

    doc2 - 100

    folder x

        doc3 - 100

        doc4 - 100

    folder y

        doc5 - 100

        doc6 - 100

        folder z

            doc 7 - 100

            doc 8 - 100

folder c

I need a DQL that uses input folder id 0b00138e806bb023 and can output like below

/repo/folder b 800
/repo/folder b/folder x 200
/repo/folder b/folder y 400
/repo/folder b/folder y/folder z 200

In the above result 800 is the sum of all docs size in folder b including all its subfolders ; 200 is the sum of all docs size in the specific folder and all its subfolders , etc.

Any ideas.

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

All comments

Michael McCollough

I took a quick go at it but have not gotten any thought on how to get the totals as you like. However, the following will give you the hierarchy and the size of the contents directly in the folder. The ouput could be take to excel to group/total as you like. So here is a "way" but not as you are directly looking for. Someone might post something smarter than this but I figured I woudl share what I have and an "alternative" way to get what you want or at least give you a starter query you can tweak more:

To use the folder id, convert the below folder('/Templates', DESCEND) to folder(ID('xxxxxxxxxxxxxxxx'),DESCEND)

SELECT fr.r_folder_path, SUM(r_full_content_size)
FROM dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr
WHERE FOLDER('/Templates', DESCEND)
AND sr.i_folder_id = fr.r_object_id
AND s.r_object_id = sr.r_object_id
AND sr.i_position = -1
AND fr.i_position = -1
AND s.i_is_deleted = 0

Output (you could group/subtotal in Excel):

r_folder_path	sum(s.r_full_content_size)
/Templates	3350193
/Templates/C2	0
/Templates/C2/Layer	86557
/Templates/C2/Merge	114688
/Templates/C2/Toc	1830
/Templates/C2/Watermark	5120
/Templates/C2/WatermarkText	16482
/Templates/C2/Xsl	29295
/Templates/D2	46158
/Templates/DCTM Reports Templates	1507629

Hicham Bahi

I don't think that there is any way to get totals and subtotals directly in DQL. You would probably need to do this in a view using the statements appropriate for your DB (e.g. ROLLUP) and then register the view in Documentum.

DocumentumT

Assuming all objects are in /Temp/Jobs folder is dm_document object.

SELECT f.r_folder_path as folder_name, count(*) as no_of_documents, SUM(e.r_full_content_size)/1024 as contentsize_in_KB
FROM dm_document e, dm_folder_r f
WHERE ANY e.i_folder_id = f.r_object_id
AND f.r_folder_path IS NOT NULL
AND f.i_position = -1
AND e.i_is_deleted = 0
AND f.r_folder_path LIKE '/Temp/Jobs%'
AND e.r_content_size >= 0
GROUP BY f.r_folder_path
UNION
SELECT 'ZZ Total documents in Temp\jobs folder', COUNT(*) as no_of_documents, SUM(e.r_full_content_size)/1024 as contentsize_in_KB
FROM dm_document e, dm_folder_r f
WHERE ANY e.i_folder_id = f.r_object_id
AND f.r_folder_path IS NOT NULL
AND f.i_position = -1
AND e.i_is_deleted = 0
AND FOLDER('/Temp/Jobs', DESCEND)
ORDER BY 1

Hicham Bahi

Works but the size for a given folder only takes into account documents directly linked to it. I think the original requirement was to have the sum for all documents including those in sub-folders.