DQL for getting count by first level folder

Stumped on DQL.

We are considering splitting up a repository into multiple repositories. Trying to determine the relative sizes for the split.

We have a Sites Cabinet, and this contains a folder for each distinct site. Each site subfolder has many subfolders.

/Sites

/Site 1

/etc ... [many subfolders and additional levels]

/Site 2

/etc ...

/Site 3

/etc ...

What I am looking for is total content size under each first level site folder (Site 1,Site 2,Site 3, etc.). The following will work if I build DQL for each first level site folder:

select sum(r_full_content_size) from dm_document (all) where (folder('/Sites/Site1',descend))

select sum(r_full_content_size) from dm_document (all) where (folder('/Sites/Site2',descend))

etc ...

However, I am looking for a way to group the results in a single query so I don't have to build the DQL out for each site individually. This next one gets me close, but unfortunately, it gives counts for each folder level under the first folder level (not just the first level):

select sum(d.r_full_content_size), f.r_folder_path from dm_document (all) d, dm_dbo.dm_folder_r f where (f.r_folder_path like '/Sites/Site%') and any d.i_folder_id = f.r_object_id and f.r_folder_path is not null group by f.r_folder_path order by f.r_folder_path

gives results for:

/Sites/Site 1

/Sites/Site 1/subfolder1

/Sites/Site 1/etc ...

/Sites/Site 2

/Sites/Site 2/subfolder1

/Sites/Site 2/etc ...

Too much information. I am looking for results limited to one level:

/Sites/Site1

/Sites/Site2

/Sites/Site3

etc...

Seems like there should be a clever way to do this.

Thanks

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

Tord

Try something like this:

SELECT f.object_name, sum(d.r_full_content_size)FROM dm_document (all) d, dm_folder fWHERE f.r_object_id IN (SELECT r_object_id FROM dm_folder WHERE FOLDER('/Sites'))AND ANY d.i_folder_id IN (SELECT r_object_id FROM dm_folder WHERE any i_ancestor_id = f.r_object_id)GROUP BY f.object_name

You might need to modify it depending on how you want to treat documents linked to multiple locations.

Hope it helps

Thanks

--Tord

All comments

sauirabh

try to limit the result using i_ancestor_id, if the number of values in this repeating attrbute are 2+ , ignore the row.

hope it helps

Saurabh

Tord

Try something like this:

SELECT f.object_name, sum(d.r_full_content_size)FROM dm_document (all) d, dm_folder fWHERE f.r_object_id IN (SELECT r_object_id FROM dm_folder WHERE FOLDER('/Sites'))AND ANY d.i_folder_id IN (SELECT r_object_id FROM dm_folder WHERE any i_ancestor_id = f.r_object_id)GROUP BY f.object_name

You might need to modify it depending on how you want to treat documents linked to multiple locations.

Hope it helps

Thanks

--Tord