Biographical articles without WP Biography tag

Fork of select count(distinct cl_to) from categorylinks by The Anome

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

playing around with the query scheme to see what effect things have on performance; a bit slower with the redundant sub-SELECT, but not much. Repeating to see whether there's some sort of schema optimization going on.

Now producing wiki markup so this can be easily cut-and-pasted into WP

Now uses LEFT JOIN so I can deal with both missing talk pages and talk pages missing categories in a single query. Had to make sure query term nesting was got right, as otherwise it selects all articles without talk pages. There is probably a neater way of doing this, but this at least works.

Note that this is far from exhaustive, but adding "X_people" for all X introduces too many false positives.

I've also tried the "People_(associated|educated)_(with|by)..." and "People_from_..." categories, and unfortunately they match groups, so I've removed thrm.

Example runs: 

Executed in 762.82 seconds as of Sat, 25 Mar 2023 23:35:23 UTC. Resultset (2879 rows) 
Executed in 236.06 seconds as of Tue, 31 Dec 2024 11:49:41 UTC. Resultset (14062 rows)

SQL

SELECT 
  CONCAT("* [[",article.page_title, "]]") -- , talk.page_id AS talk_page_id
FROM
  page AS article
  LEFT JOIN page AS talk ON talk.page_title = article.page_title AND talk.page_namespace = 1  AND talk.page_is_redirect = 0
WHERE
 -- article.page_id % 10 = 2
  article.page_namespace = 0
  AND article.page_is_redirect = 0
  AND NOT article.page_title RLIKE "^(Lists?_of|[0-9]{4})_.*$" -- no dated events or lists
  AND EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = article.page_id and 
              (cl_to = "Living_people"
               OR cl_to RLIKE "^(Date|Year)_of_(birth|death)_(missing|unknown).*$"
               OR cl_to RLIKE "^[0-9][^_]+_(births|deaths)$")) -- make sure we don't catch things like 'protest-related deaths'
  AND (
    (talk.page_id IS NULL) 
    OR ((talk.page_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = talk.page_id 
                 and (cl_to LIKE "%Biography_articles" 
                      OR cl_to LIKE "%biography_articles" 
                      OR cl_to LIKE "%WikiProject_Biography%"))))

ORDER BY article.page_title

​x
 
SELECT   CONCAT("* [[",article.page_title, "]]") -- , talk.page_id AS talk_page_idFROM  page AS article  LEFT JOIN page AS talk ON talk.page_title = article.page_title AND talk.page_namespace = 1  AND talk.page_is_redirect = 0WHERE -- article.page_id % 10 = 2  article.page_namespace = 0  AND article.page_is_redirect = 0  AND NOT article.page_title RLIKE "^(Lists?_of|[0-9]{4})_.*$" -- no dated events or lists  AND EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = article.page_id and               (cl_to = "Living_people"               OR cl_to RLIKE "^(Date|Year)_of_(birth|death)_(missing|unknown).*$"               OR cl_to RLIKE "^[0-9][^_]+_(births|deaths)$")) -- make sure we don't catch things like 'protest-related deaths'  AND (    (talk.page_id IS NULL)     OR ((talk.page_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = talk.page_id                  and (cl_to LIKE "%Biography_articles"                       OR cl_to LIKE "%biography_articles"                       OR cl_to LIKE "%WikiProject_Biography%"))))​ORDER BY article.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 Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...