BATCH CTE and recursion - CTE table doesn't exists

Transact-SQL
1

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;
2
3

hi

hope this helps

create tables sample data

CREATE TABLE IF NOT EXISTS CONTENT_ENTRIES (
entry_id INT AUTO_INCREMENT PRIMARY KEY,
content_type_id_fk VARCHAR(255) NOT NULL,
entry_type VARCHAR(255) NOT NULL,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
publishedVersion INT DEFAULT 1,
revision INT DEFAULT 1,
locale VARCHAR(10) DEFAULT 'en-US'
);

CREATE TABLE IF NOT EXISTS ENTRIES_REFERENCES (
reference_id INT AUTO_INCREMENT PRIMARY KEY,
entry_id_fk INT NOT NULL,
reference_entry_id INT NOT NULL,
FOREIGN KEY (entry_id_fk) REFERENCES CONTENT_ENTRIES(entry_id),
FOREIGN KEY (reference_entry_id) REFERENCES CONTENT_ENTRIES(entry_id)
);

INSERT INTO CONTENT_ENTRIES (content_type_id_fk, entry_type, locale) VALUES
('siteDetails', 'mainSite', 'en-US'),
('siteDetails', 'subSiteA', 'en-US'),
('relatedContent', 'blogPost1', 'en-US'),
('relatedContent', 'blogPost2', 'en-US'),
('relatedContent', 'blogPost3', 'en-US');

-- Insert sample data into ENTRIES_REFERENCES to create relationships
INSERT INTO ENTRIES_REFERENCEs (entry_id_fk, reference_entry_id) VALUES
(1, 2), -- mainSite references subSiteA
(2, 3), -- subSiteA references blogPost1
(3, 4), -- blogPost1 references blogPost2
(4, 5), -- blogPost2 references blogPost3
(1, 3); -- mainSite references blogPost1

CREATE INDEX idx_content_type ON CONTENT_ENTRIES (content_type_id_fk);
CREATE INDEX idx_entry_id_fk ON ENTRIES_REFERENCES (entry_id_fk);

;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
    OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY -- T-SQL paging
),
RecursiveEntryHierarchy AS (
    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
    
    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
OPTION (MAXRECURSION 0);