Untitled query #84715

SQL

WITH RECURSIVE subcategories AS (
    SELECT cl_to AS subcategory
    FROM categorylinks
    WHERE cl_from = (SELECT page_id FROM page WHERE page_title = 'Iraq' AND page_namespace = 14)

    UNION ALL

    SELECT cl_to
    FROM categorylinks
    JOIN subcategories ON categorylinks.cl_from = (SELECT page_id FROM page WHERE page_title = subcategories.subcategory AND page_namespace = 14)
),
articles AS (
    SELECT page.page_id, page.page_title, page.page_len
    FROM page
    JOIN categorylinks ON page.page_id = categorylinks.cl_from
    WHERE page.page_namespace = 0
        AND page.page_is_redirect = 0
        AND categorylinks.cl_to IN (SELECT subcategory FROM subcategories)
),
filtered_articles AS (
    SELECT a.page_id, a.page_title, a.page_len, COUNT(ll.ll_lang) AS link_count
    FROM articles a
    LEFT JOIN langlinks ll ON a.page_id = ll.ll_from
    WHERE NOT EXISTS (
        SELECT 1
        FROM langlinks t
        WHERE t.ll_lang = 'ar'
            AND t.ll_from = a.page_id
    )
    GROUP BY a.page_id, a.page_title, a.page_len
)
SELECT CONCAT_WS('||',
        CONCAT('[[:en:', page_title, ']]'),
        link_count,
        page_len
    ) AS result
FROM filtered_articles
ORDER BY page_len DESC, link_count DESC;
SQL

Checking query status...
