Hi
i've a use case to get all entries of a content type , along with their nested references (same or other entries (one or more) ). i tried to use batch(with clause) and cTE queries as below. when i ran the query in workbench 8.0 and in sequelize orm framework node js , i get table not exists issue. Yet , i've been able to successfully verify with a simple CTE query.
appreciate any inputs for resolution.
WITH EntryBatch AS (
SELECT
ce.entry_id,
ce.content_type_id_fk,
ce.entry_type,
ce.createdAt,
ce.updatedAt,
ce.publishedVersion,
ce.revision,
ce.locale
FROM
CONTENT_ENTRIES ce
WHERE
ce.content_type_id_fk = 'siteDetails'
ORDER BY
ce.createdAt
LIMIT 100 OFFSET 0 -- Adjust LIMIT and OFFSET for batching
),
-- Step 2: Recursive CTE to fetch nested references for the batch
RecursiveEntryHierarchy AS (
-- Anchor member: Select the initial batch of entries
SELECT
eb.entry_id,
eb.content_type_id_fk,
eb.entry_type,
eb.createdAt,
eb.updatedAt,
eb.publishedVersion,
eb.revision,
eb.locale,
er.reference_entry_id
FROM
EntryBatch eb
LEFT JOIN
ENTRIES_REFERENCES er ON eb.entry_id = er.entry_id_fk
UNION ALL
-- Recursive member: Select the referenced entries
SELECT
ce.entry_id,
ce.content_type_id_fk,
ce.entry_type,
ce.createdAt,
ce.updatedAt,
ce.publishedVersion,
ce.revision,
ce.locale,
er.reference_entry_id
FROM
CONTENT_ENTRIES ce
INNER JOIN
ENTRIES_REFERENCES er ON ce.entry_id = er.reference_entry_id
INNER JOIN
RecursiveEntryHierarchy reh ON reh.reference_entry_id = ce.entry_id
)
SELECT
reh.entry_id,
reh.content_type_id_fk,
reh.entry_type,
reh.createdAt,
reh.updatedAt,
reh.publishedVersion,
reh.revision,
reh.locale
FROM
RecursiveEntryHierarchy reh;