State name for the JSR Query

Rhythmyx Technical Discussion
1

I am in the process of generating a report from the following query that I am running in the Rhythmyx JSR Test tool to get a list of all the content items of genericPage type.

select rx:displaytitle, rx:sys_title, rx:sys_contentid,jcr:sys_statename, jcr:path
from rx:genericPage
where jcr:path =‘//Sites/%’
order by jcr:path

I also want to retreive the state of the content item, whether it is in DRAFT, Quick Edit, or PUblic state. What is the name of the state attribute? I tried using sys_statename, sys_contentstateid…Neither of them worked.

thanks
Manvinder

2

Manvinder,

You can see all of the available fields for a content type by using the * wildcard in place for the selected fields. I don’t see a state name that’s systematically populated. You could see if rx:sys_workflowid would work for you.

3

Thanks Riley…
Actually I have a need to generate a list of all the pages that are currently in PUBLIC state. I tried to use the Content Explorer Search tool, and used the Public filter. However, it appears to be failing probably because we have
more than 3000+ pages in all…Is there any other way to generate the list? Or I can run a SQL on the Database tables. Not sure which table has the state mapping, as the content type table does not has the state data in it.

thanks
Manvinder

4

Have you tried creating a content list for this content type then assigning the content list to an edition, and instead of running the edition you can do a preview of the content list to see all the items returned?

5

Thanks Riley…Yes, it shows what I need…It never occured to me that we can use preview for generating a report. However, it only shows the sys title, content id, location, modify date, user, and content type.
We have a field called displaytitle, which is a shared field…We need to show that field in the report as well…Not sure if there is any workaround for that as well or not? Or any different way to generate a report.

Thanks Again
Manvinder

6

I am not aware of a method to change how the content listing is generated. You could try the following SQL on your database.

SELECT contentstatus.contentid FROM CONTENTSTATUS WHERE contentstatus.contentstateid in (SELECT DISTINCT STATEID FROM STATES WHERE STATENAME = 'Public') AND contentstatus.contenttypeid IN (SELECT CONTENTTYPEID FROM CONTENTTYPES WHERE CONTENTTYPENAME = 'contentTypeName');
7

Thanks Riley…This did work for me. The only modification I would suggest is to include the workflow id as well to the SQL. The reason being atleast in our case, we have multiple workflows, so state name PUBLIC appears several times

8

Is there a way to include the folder path as well in the below SQL. I need to know the folder path for each item as well.

SELECT contentstatus.contentid FROM CONTENTSTATUS WHERE contentstatus.contentstateid in (SELECT DISTINCT STATEID FROM STATES WHERE STATENAME = ‘Public’) AND contentstatus.contenttypeid IN (SELECT CONTENTTYPEID FROM CONTENTTYPES WHERE CONTENTTYPENAME = ‘contentTypeName’);