How to get count of different types of document in a folder using DQL

Question

I have two types of docs say obj_type_A and obj_type_B which may or may not be in a folder of type obj_type_fldr.

I need to get the count of both the docs present in the folder of type obj_type_fldr.

If it had an sql we can write sub-query in select clause. But DQL didn't support this function.

Answer 1

The trick here is to utilize the r_object_type attribute of dm_sysobject. So what you want is probably only achievable if both of your custom types are descendants of dm_sysobject. And in that case, this is how you do it.

Basic count

SELECT count(r_object_id), r_object_type 
FROM dm_sysobject
WHERE ANY i_folder_id IN (
    SELECT r_object_id 
    FROM obj_type_fldr
) 
AND r_object_type IN ('obj_type_A','obj_type_B') 
GROUP BY r_object_type

Example output:

dm_attr_0001  r_object_type
------------  --------------------------------
         271  obj_type_A
         195  obj_type_B
(2 rows affected)

Count per folder

Additionally, if you want to count how many documents are in each folder, just add i_folder_id to your query like this:

SELECT count(r_object_id), r_object_type, i_folder_id 
FROM dm_sysobject
WHERE ANY i_folder_id IN (
    SELECT r_object_id 
    FROM obj_type_fldr
) 
AND r_object_type IN ('obj_type_A','obj_type_B') 
GROUP BY r_object_type, i_folder_id

Example output:

dm_attr_0001  r_object_type     i_folder_id
------------  ----------------  ----------------
         271  obj_type_A        0b00000080000001
         100  obj_type_B        0b00000080000001
          95  obj_type_B        0b00000080000002
(3 rows affected)

You can of course extend this query with additional clauses, e.g. in order to count only documents that are within a specific path, but you get the general idea.