Query to find list of objects in folder along with its Folder Path

Occasionally we require to find the list of all objects from a folder and also retrieve their exact folder path with in the same query.

This can be achieved easily through a DFC Program. But its a little tricky when you want it through a a DQL Query. As dm_sysobject stores only the folder id (i_folder_id) of the object instead of the folder path.

The folder path is hidden in the dm_folder object and is a repeating attribute. So we need to query dm_folder for r_folder_path. The issue in DQL is you can’t select repeating attributes when you join multiple types. You will hit DM_QUERY2_E_REPEAT_TYPE_JOIN if you do so.

Lets see what DFC can do and how to approach the same with DQL.

DFC code snippet looks as below:

IDfFolder folder = (IDfFolder) session.getObjectByPath(<<folderpath>> );
if (folder != null) {
     getContents(session, folder, docs);
     System.out.println("Total Number of Files : "+docs.getSize() );
}
 ....

private List getContents(IDfSession session, IDfFolder folder, List docs) throws DfException, IOException {
   // get all the r_object_id
   IDfCollection collection = folder.getContents("r_object_id");
   if (collection != null) {
      while (collection.next()) {
	String objectId = collection.getString("r_object_id");
	IDfSysObject object = (IDfSysObject) session.getObject(new DfId(objectId));
	if (object.getTypeName().equals("dm_folder" ) || object.getType().isSubTypeOf("dm_folder" )) {
           getContents(session, (IDfFolder) object, docs, writer);
	} else {
           IDfFolder folderObj = (IDfFolder) session.getObjectByQualification("dm_folder where r_object_id = '+ object.getString("i_folder_id" ) + "'");
           if (folderObj != null) {
	      buffer = object.getObjectName() + "\t"+ object.getOwnerName() + "\t"+
                                  folderObj.getString("r_folder_path" ) + "\t"+ object.getModifyDate();
           }
	}
    }
    collection.close();
   }
}

In DQL the same can be achieved as below:

DQL> select A.r_object_id, A.object_name, B.r_folder_path from dm_document A, dm_folder_r B where any A.i_folder_id = B.r_object_id and B.r_folder_path like ‘%/System/%’;

What we are trying to achieve is to join the dm_document type repeating attribute ‘i_folder_id’ and a dm_folder single value attribute table. This way we don’t end up querying the r_folder_path a repeating attribute. If we would have queried dm_folder type (instead of dm_folder_r) we would have hit the DQL restriction of DM_QUERY2_E_REPEAT_TYPE_JOIN error. However querying the underlying table enables us to pass through the DQL translator for _r table (just like any registered table concept).