find AWB Critical CSS correspondance between contents of cache table and URLS

find-awb-urls.sql

-- Query to retrieve CSS keys and corresponding URLs from WordPress tables
-- tested against mysql 5.x
-- table prefix is wpjg
-- generated with assistance from Claude


SELECT 
    c.css_key,
    CASE
        WHEN c.css_key = 'homepage' THEN home.option_value
        WHEN p.ID IS NOT NULL THEN 
            CONCAT(home.option_value, 
                   CASE 
                       WHEN p.post_type = 'page' THEN ''
                       ELSE '/post'  -- Adds '/post' for non-page post types
                   END,
                   '/', p.post_name)
        ELSE CONCAT(home.option_value, '/', c.css_key)  -- Fallback for unmatched keys
    END AS full_url
FROM 
    wpjg_awb_critical_css c
LEFT JOIN 
    wpjg_posts p ON c.css_key = CAST(p.ID AS CHAR) COLLATE utf8mb4_unicode_520_ci
    -- ^ Joins with posts table, casting ID to string and setting collation to avoid mismatch
CROSS JOIN 
    (SELECT option_value FROM wpjg_options WHERE option_name = 'home') AS home
    -- ^ Retrieves the home URL once for all rows
ORDER BY 
    c.css_key;