User:The Earwig/Sandbox/Orphaned archives

This page lists likely orphaned talk page archives. These are talk page archives that are not linked to from a non-archive talk page, including via redirects. Talk pages are considered archives if they have "/Archive" in the title.

SQL queries

WITH adopted_talks AS (
    -- first cte:
    -- talk archives with a direct link from a non-archive talk page
    SELECT
        child.page_id AS talk_id,
        child.page_is_redirect AS talk_is_redir
    FROM pagelinks
    INNER JOIN page AS base
        ON pl_from = base.page_id
    INNER JOIN page AS child
        ON pl_title = child.page_title AND pl_namespace = child.page_namespace
    WHERE
        base.page_namespace = 1
        AND child.page_namespace = 1
        AND base.page_title NOT LIKE "%/Archive%"
        AND child.page_title LIKE "%/Archive%"
),
adopted_talks_indirect AS (
    -- second cte:
    -- talk archives with an indirect link from a non-archive talk page;
    -- that is, a page that redirects to the archive has a direct link to a
    -- non-archive talk page
    SELECT
        child.page_id AS talk2_id
    FROM pagelinks
    INNER JOIN adopted_talks
        ON pl_from = talk_id
    INNER JOIN page AS child
        ON pl_title = child.page_title AND pl_namespace = child.page_namespace
    WHERE
        talk_is_redir = 1
        AND child.page_namespace = 1
        AND child.page_title LIKE "%/Archive%"
)
-- generate the list of talk archives that are neither directly nor indirectly
-- referenced from a non-archive talk page
SELECT
    CONCAT("Talk:", REPLACE(page_title, "_", " "))
FROM page
LEFT JOIN adopted_talks ON page_id = talk_id
LEFT JOIN adopted_talks_indirect ON page_id = talk2_id
WHERE
    page_namespace = 1
    AND talk_id IS NULL
    AND talk2_id IS NULL
    AND page_is_redirect = 0
    AND page_title LIKE "%/Archive%"
    AND page_title NOT LIKE "%/Archive_index";