SQL for retrieving a list of content items associated with a content item (via slots)

Rhythmyx Technical Discussion
1

Hi,
IS there a sql query that can be run against the rhythmyx db to retrieve a list of content items associated with another content item (via slots). Any help would be useful…

2

There is… Although, using the “Active Assembly Table Editor” or even “View Content” is just as quick.

Content relationships are stored in the “psx_objectrelationship” table.
Slot relationships have a “config_id” of 1.
The item containing the slot is specified by “owner_id” and the items in the slot are specified by “dependent_id”
Slot relationships are duplicated for each new revision. Presumably, this is to ease the roll-back process.

So… to determine all current items related to a specific item, you’d want to do something like:

select distinct dependent_id from psx_objectrelationship where config_id = 1 and owner_id = ? and owner_revision in (select currentrevision from contentstatus where contentid = ?)

And to determine all items that were ever attached to a content item:

select distinct dependent_id from psx_objectrelationship where config_id = 1 and owner_id = ?

3

Thanks very much Rushing… This is very helpful … I was using something similar but was not sure if that was a good idea or not (below is the query)
select * from psx_objectrelationship
where owner_id=content id
and slot_id in (547)
and owner_revision = (select public_revision from contentstatus where contentid=content id);

But with the above query, I will need to know the slot ids and so I could not make it generic, guess I was missing the ‘config_id’ piece. Also, should I use the public_revision field or the current version?

Having done that piece, would it be possible to get the details of the content item (atleast the location of the item or the published URL) that are attached to the content, the complicated thing being that there may be different content types attached to the slot and those might be stored in multiple tables, so how to retrieve that kind of information.

4

Item location in the content explorer is best looked up using the “Impact Analysis” tool in the content explorer. While possible, it’s kind of messy to build a path to a content item using the psx_objectrelationship table.

And, yes, use whichever revision number you find most useful… public, current or edit.

5

Is it not possible to retrieve content data by just feeding the content id to a specific table?

6

Amit,

The SQL query below will return the Slot ID, the Slot Name, as well as a slot item’s Content ID, Name, and Content Type, for a given Content ID (note the comment on the second to last SQL line).


SELECT 
  SL.SLOTID AS SLOT_ID, 
  SL.SLOTNAME AS SLOT_NAME,
  REL.DEPENDENT_ID AS SLOT_ITEM_CONTENTID,
  CS.TITLE AS SLOT_ITEM_NAME,
  CT.CONTENTTYPENAME AS SLOT_ITEM_CONTENTTYPE_NAME
FROM 
  PSX_OBJECTRELATIONSHIP REL,
  RXSLOTTYPE SL,
  CONTENTSTATUS CS,
  CONTENTTYPES CT
WHERE 
  SL.SLOTID = REL.SLOT_ID
  AND REL.DEPENDENT_ID = CS.CONTENTID
  AND CS.CONTENTTYPEID = CT.CONTENTTYPEID
  AND REL.OWNER_REVISION = CS.CURRENTREVISION
  AND REL.SLOT_ID IS NOT NULL
  AND REL.OWNER_ID = '#####' -- Change ##### to Content ID Number
ORDER BY SL.SLOTID

-Paul

7

Content data is spread out between the contentstatus table, content-specific tables and shared tables. You can certainly query against them if you know the names of the tables you want to add to your query. It sounded like you wanted to find a path to the content item, though, and this data is not stored in human-readable form anywhere.

8

Thanks for the info …

I was planning on to use PSX_PUBLICATION_DOC table for retrieving the location information for a content and soon realized that it may not be a good way since the data is probably fed to this table during publishing and may or may not be accurate, also purging keeps happening in this table, so I may or may not get the info for all the content items (if the content item has not been published in a long time, but the content may still be available on the site) … Is that a right assumption?