How to retrieve the r_folder_path of documents using Documentum Rest /Search Endpoint

Hello Techies,

I am currently exploring Documentum REST and have a requirement to retrieve the r_folder_path along with the document results from the search endpoints. Below is an example of a search endpoint I used, but the results do not include the r_folder_path.

To fetch the r_folder_path, I tried using the /dql endpoint, and it worked successfully with the following query:
http://localhost:8080/dctm-rest/repositories/REPO/?dql=SELECT r_folder_path FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='0944a43250005555')

However, my requirement is to use the /search endpoint, so the results should directly include the r_folder_path attribute.

Any suggestions or insights would be greatly appreciated!

Thanks,
Kaushal

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

Hicham Bahi

I don't think that there is any way to do this out-of-the-box. The folder path is not stored directly with the document : instead you have to go via the i_folder_id to get a list of folders to which a document is linked. Using these IDs you can get the folder path.

kaushaldhabadge

Thanks @Hicham Bahi for providing your input.

Is there way we can use DQL and search endpoint together to because I want to use the facets ultimately where I will need the r_folder_path to be displayed.

Thanks, Kaushal

Hicham Bahi

I don't think so. In order to retrieve r_folder_path in the same query as your documents, you need to perform a join between dm_document (or your custom type) and dm_folder_r. Unfortunately, it seems like the dql resource cannot handle it (I tried it with REST 23.x and it didn't work). Perhaps you can give it a try or else you can open a ticket with OpenText support.

I haven't tried with the search resource but I also think that it wouldn't work, since xPlore also doesn't store the folder path directly with the documents.

kaushaldhabadge

Hi @Hicham Bahi,

I successfully tested it using DQL by joining dm_document and dm_folder. It worked with the following sample endpoint:

http://localhost:8080/dctm-rest/repositories/REPO/?dql=SELECT r_folder_path FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='0900000000000000')

Response Body:
{………

..

"r_folder_path": [ "/Cabinet1/Temp Document/SOP/Project Documents/Sample Documents/Test Folder" ] }

Hicham Bahi

If that's what you were after then perfect. It's not really a join though. I thought you wanted the folder path in the same result set as you documents. I tried something like this:

select d.r_object_id as r_object_id, d.object_name as object_name, f.r_folder_path as folder_path from dm_document d, dm_folder_r f where <your doc criteria here> and any d.i_folder_id = f.r_object_id and f.r_folder_path is not null

This works in DQL but not when passed via Documentum REST dql resource. It just returns the repository resource without executing the DQL. Not sure what the issue is. Had a quick look at logs but saw no error.

kaushaldhabadge

Thank you @Hicham Bahi.

I actually want the result set of documents with r_folder_path, that too when I search for the documents using /search endpoints.

Anyways, Thanks a lot for your attention on this. I will explore little more and at the end will log the ticket with AMS Team.

Michael McCollough

Here are a couple of ones I have used. Here I wanted all the documents under a folder with the path AND I only wanted the primary folder path (first i_folder_id in index):
http(s)://host/{rest-app-name}/repositories/{reponame}?dql=SELECT%20s.object_name,%20s.r_modifier,%20s.r_modify_date,%20fr.r_folder_path%20FROM%20dm_sysobject%20s,%20dm_sysobject_r%20sr,%20dm_folder_r%20fr%20WHERE%20FOLDER(%27/Templates/C2%27,%20DESCEND)%20AND%20sr.i_folder_id%20=%20fr.r_object_id%20AND%20s.r_object_id%20=%20sr.r_object_id%20AND%20sr.i_position%20=%20-1%20AND%20fr.i_position%20=%20-1%20AND%20s.i_is_deleted%20=%200%20ORDER%20BY%20fr.r_folder_path

Note: You can type: SELECT s.object_name, s.r_modifier, s.r_modify_date, fr.r_folder_path FROM dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr WHERE FOLDER('/Templates/C2', 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 ORDER BY fr.r_folder_path and the browser will automatically add the encoding.

@Hicham Bahi's query worked for me in rest using:

dql=SELECT%20d.r_object_id%20AS%20r_object_id,%20d.object_name%20AS%20object_name,%20f.r_folder_path%20AS%20folder_path%20FROM%20dm_document%20d,%20dm_folder_r%20f%20WHERE%20FOLDER(%27/Templates%27)%20AND%20ANY%20d.i_folder_id%20=%20f.r_object_id%20AND%20f.r_folder_path%20IS%20NOT%20NULL

Michael McCollough

Note, when doing joins with folders (or any repeating values), unless you are looking only for the primary folder path, you can get duplicate r_object_id for documents as documents MAY be linked to more than one path. Whild you can get comma delimitted repeating attributes by using simple DQL and including r_object_id, it does not apply to joins.

How might you get around this if you are looking for comma seperated folder paths? In the past I have created a view in the database (can be specific to the database depending on what you do). I have used PACK on MSSQL to let me give XML layouts of data and other features so I could query and get the data I wanted in lookup lists, reports, etc.

One thing of note here is that most will tell you you can create a registred table with just one paremeter, letting the other columns just default. However, when doing a datatype that DCTM migth not understand you can generally include the column as a string in the creation of the registered table. For instance me view with simple:

select xml_value from dm_dbo.my_registred where x=y

Woudl give me (from DCTM) an invalid data type, by, in the registred table creation, specifying it as a string it allowwed it to translate/understand it.

This is all from top of my head so may be a few details missing but should be majority accurate :)