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

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 sub_type
WHEN 2 THEN 'Presentation' -- item_type 4, sub_type 2
WHEN 3 THEN 'Publication' -- item_type 4, sub_type 3
END AS ContainerType
FROM content_tbl_item
WHERE item_type = 4
AND sub_type IN (2, 3)
) pp -- Presentations and Publications
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 pp.ContainerName, i.name;

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.