This query is marked as a draft This query has been published by Retro.

SQL

AخA
 
USE enwiki_p;
SELECT
  /* I need to make this namespace prefixing into a separate function, because I've used this twice. */
  CASE page_namespace
    WHEN 10 THEN CONCAT("Template:", page_title)
    WHEN 11 THEN CONCAT("Template talk:", page_title)
  END AS "Page title",
  R,
  /* I would like to generate a list of redirects as a column. */
  (
    SELECT GROUP_CONCAT(page2_title SEPARATOR "; ")
    FROM redirect
    JOIN (
      SELECT
        page_id AS page2_id,
        /* Repeated 4 lines from above; not DRY */
        CASE page_namespace
          WHEN 10 THEN CONCAT("Template:", page_title)
          WHEN 11 THEN CONCAT("Template talk:", page_title)
        END AS page2_title
      FROM page
    ) AS page2 ON page2_id = rd_from
    WHERE rd_namespace = page_namespace AND rd_title = page_title
  ) AS "List of redirects"
  /* Temporarily(?) hide
  GROUP_CONCAT(cl_to SEPARATOR "; ") AS "Categories (of primary page)" */
FROM
(  
  SELECT
    page_namespace,
    page_title,
    CASE page_is_redirect
      WHEN 1 THEN "(R)"
    END AS R,
    /*
    Overcomplicated redirect/talk page primary page id determination code
    Much trouble could have been saved if I excluded talk pages and redirects entirely,
    but I definitely wanted redirects,
    and thought talk pages might also be useful because they often note previous TfDs.
    I'm fairly sure I've done an exhaustive TfD search of section templates,
    but it never hurts to have a visual reminder to recognize such templates.
    */
    CASE
      WHEN page_is_redirect = 1 THEN rd_target_page_id
      WHEN page_namespace = 11 THEN talk_primary_page_id
      ELSE page_id
    END AS primary_page_id,
    page_id
  FROM page
  LEFT JOIN redirect ON page_id = rd_from
  LEFT JOIN (
    SELECT
      page_id AS rd_target_page_id,
      page_title AS rd_target_page_title
    FROM page
    WHERE page_namespace = 10
  ) AS rd_target_page ON rd_target_page_title = rd_title
  LEFT JOIN (
    SELECT
      page_id AS talk_primary_page_id,
      page_title AS talk_primary_page_title
    FROM page
    WHERE page_namespace = 10
  ) AS talk_primary_page ON talk_primary_page_title = page_title
  WHERE
    (
      page_namespace = 10
      OR page_namespace = 11
    )
    AND page_title LIKE "%section%"
    AND page_title NOT LIKE "%/doc"
    AND page_title NOT LIKE "%/sandbox"
    AND page_title NOT LIKE "%/testcases"
    /* AND page_is_redirect = 0 /* Temporary try, but it timed out the query for some reason. */
) AS section_pages
LEFT JOIN categorylinks ON cl_from = primary_page_id
/* Get all redirects to each page
Too inefficient.
I created a more efficient method above.
*/
/*LEFT JOIN (
  SELECT
    rd_title AS rd_to_page_target_title,
    page_title AS rd_to_page_title
  FROM page
  LEFT JOIN redirect ON rd_from = page_id
  WHERE
    page_namespace = 10
    AND rd_to_namespace = 10
) AS rd_to_page ON rd_to_page_target_title = page_title*/
WHERE
  NOT EXISTS (
    SELECT 1
    FROM categorylinks
    WHERE
      cl_to IN (
        "Sports_club_departments_sidebar_templates",
        "Templates_for_railway_lines_of_India",
        "Wikipedia_progress_templates",
        "Scotland_mountains_and_hills_navigational_boxes",
        "Archival_templates",
        "Collapse_templates",
        "Game_log_templates",
        "Anatomy_templates",
        "Internal_link_templates",
        "West_Japan_Railway_Company_templates",
        "Redirect_templates",
        "Major_League_Baseball_game_log_templates",
        "WikiProject_New_York_City_Public_Transportation_templates",
        "Section_and_anchor_link_formatting_templates",
        "Philippine_Basketball_Association_templates",
        "Palakkad_railway_division"
      )
      AND cl_from = primary_page_id
  )
  /*
  Talk page redirect - If I had double-followed to the primary page, this would be unnecessary.
  */
  AND page_title NOT LIKE "Monthly_clean-up_category/%"
  AND page_title NOT LIKE "Did_you_know_nominations/%"
  AND page_title NOT LIKE "Infobox%"
  AND page_title NOT LIKE "Taxonomy/%"
  AND page_title NOT IN (
    "ABL_game_log_section",
    "Advanced_base_section_dock_(ABSD)",
    "Afc_section_cleared",
    "Alternating_rows_table_section",
    "BAC-LAC/Header_sections",
    "Big_Ten_Conference_basketball_student_section_navbox",
    "Category_intersection",
    "Cat_main_section",
    "Category_main_section",
    "Clean-up_type_category/messages/pages_with_missing_lead_section",
    "Cleanup/Articles_with_probable_cleanup_sectionon_talk_page",
    "Cleanup_template_documentation_see_also_section_generic_list",
    "Cleanup_template_documentation_see_also_section_generic_list/Archive_1",
    "Collapsible_section_option",
    "Collapsible_sections_option",
    "Cotton_portal_section_heading",
    "Create_talk_page_section",
    "DANFSsection",
    "Dissection",
    "Editnotices/Page/Help_talk:Labeled_section_transclusion",
    "Etymology_section",
    "Female_genitalia_frontal_cross-section_numbered",
    "German_law_section",
    "Guangzhou_Metro_lines/sections",
    "Image_filter_sections",
    "Invitation_to_edit/section",
    "Kabe_Line_suspended_section",
    "Korea_Armed_Forces_Athletic_Corps_sections",
    "Liberty_ship_by_hull_list_section",
    "London_Transport_portal_section_heading",
    "Monthly_clean_up_category/Messages/Article_sections_to_be_split",
    "NBA_game_log_section",
    "NBL_game_log_section",
    "Nelson_section_RDT",  /* Another railroad routemap template */
    "MPBL_game_log_section",
    "Navbox_with_collapsible_sections",
    "New_section",
    "Panellinios_G.S._sections",  /* Waiting to be categorized */
    "Policy_section_top",
    "Policy_section",
    "Portalsection",
    "Project_section_header",
    "Requested_articles_unsorted_section",
    "RD_section",
    "See_section",  /* Talk page redirect (see above) */
    "Shoranur_-_Cochin_Harbour_section",
    "Shoranur_–_Cochin_Harbour_section",
    "Stations_on_the_Shoranur_-_Cochin_Harbour_section",
    "Stations_on_the_Shoranur_–_Cochin_Harbour_section",
    "SOIUSA_sections_of_the_Alps",
    "TH_mid_section",
    "Transcluded_sections_for_the_visa_articles",
    "Vijayawada-Gudivada_section",  /* Talk page redirect (see above) */
    "WHA_section_toc",
    "WNBA_game_log_section",
    "WPESC_Newsletter_section_header",
    "WPMILHIST_Newsletter_section_header",
    "WPMILHIST_Newsletter_section_header_2"
  )
GROUP BY page_id
ORDER BY R, page_namespace DESC, page_title
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...