Folder Path

Rhythmyx Technical Discussion
1

Which table in the Database has the item path for each content item?
I am trying to execute a SQL Query on the Database to generate a report with various fields. One of the critical fields I needs is the folder path. Just like in JCR Query where we can pass a condition for the path like //Sites/ABCSite%, is there anything similar Database field as well?

thanks
Manvinder

2

The folder path is built by looking at the parent folder item in psx_objectrelationship and then iterating through until you get to one of the top folders (Sites or Folders). There isn’t one table that just lists the folder path for you to access via a straight query… at least not that i know of…

3

Thanks Jitendra…
I wish there was atleast the state field option for the JSR query, so basically we are ending up running 2 reports, one from the DB and another from JSR, and then merging them to get a report that is filtered by state, as well as have the folder path.

4

There is no reason why someone with sufficient sql knowledge can’t write a procedure / function that will return a folder path of an item it is a relatively straight forward loop…

5

I took a stab at writing a database function for this but it ended up being way too slow on an instance with any size for real time use. The product builds the Folder Cache in memory when the server starts up and uses that internally instead of SQL at runtime.

If the use case is for a Report, I would recommend writing this a SQL script that populates a TEMP table in the database with the folder / item paths/ states and then joining to that in the SQL for the Report. Maybe rebuilding that table as a scheduled SQL task nightly or something.

-n

6

It may be easier to do the loop with Java than a SQL procedure…I don’t know enough about SQL procedures yet, but I assumed that the logic would be similar. Technically, you could combine both reports in a single jsp page (and use something similar to $rx.location.folderPath() )