How to get a List of all Objects used in Present and Publish. From Tips&Tricks 3/21

Let me connect you
Community_chris
updated 1 yr ago

Pyramid_TipsandTricks_2021_Volume3_FINAL

Sneak: How to get a list of all objects in Present and Publish

The Tips&Tricks Series is produced by the Customer Success Team. It highlights several easy to follow instructions and range from simple tasks to more advanced use cases.

How to get a List of all Objects used in Present and Publish

When to Use: To get a list of all Discover objects being used by Present and Publish objects (i.e. SQL version of Lineage).

SELECT DISTINCT
 pp.ContainerID
, pp.ContainerName
, pp.ContainerType
, i.id AS ContentID
, i.name AS ContentName
, CASE i.item_type WHEN 3 THEN ‘Discover’ ELSE ‘Illustration’ END AS ContentType
FROM (
SELECT id AS ContainerID
, name AS ContainerName
, CASE WHEN (item_type = 4 AND sub_type = 2) THEN ‘Presentation’ ELSE ‘Publication’ END
 AS ContainerType
FROM content_tbl_item
WHERE (item_type = 4 AND sub_type = 2)
OR (item_type = 4 AND sub_type = 3)
) pp --Get List of Present and Publish Objects
INNER JOIN content_tbl_container_data cd ON pp.ContainerID = cd.container_id
INNER JOIN content_tbl_document d ON cd.id = d.container_data_id
INNER JOIN content_tbl_item_usages iu ON d.id = iu.parent_id
INNER JOIN content_tbl_item i ON iu.child_id = i.id
ORDER BY ContainerName, ContentName

If you have questions about these tips (or improvements) please let us know in a comment.

P.S. We will post previous Tip&Tricks as well as all new ones to keep this information active and available to all users.