Probable people articles with talk pages not assigned to WikiProjects

Fork of Articles with talk pages but not assigned to WikiProjects by The Anome

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

Detects articles with talk pages that are not assigned to any Wikiprojects, and then filters them further to try to detect articles that might be about people.

Note: deliberately ignores the special case of non-redirect pages with talk page redirects; these will be dealt with at the target talk page anyway

Example March 2023 runtime:  373 seconds, result set 1088 rows

SQL

SELECT
  article.page_title,
  article.page_id,
  talk.page_id AS talk_id
FROM
  page AS article
  LEFT JOIN page_props ON pp_page = article.page_id
  AND pp_propname = 'disambiguation'
  INNER JOIN page AS talk ON talk.page_title = article.page_title
WHERE
  article.page_namespace = 0
  AND talk.page_namespace = 1
  AND pp_page IS NULL
  AND article.page_is_redirect = 0
  AND talk.page_is_redirect = 0
  AND NOT (article.page_title LIKE "List_of_%")
  AND NOT (article.page_title LIKE "Lists_of_%")
  AND NOT EXISTS ( 
    SELECT
      1
    FROM
      categorylinks
    WHERE
      article.page_id = cl_from
      AND (
        cl_to IN (
          "Temporary_maintenance_holdings",
          "Candidates_for_speedy_deletion",
          "Requested_RD1_redactions"
        )
        OR cl_to LIKE "%_names"
        OR cl_to LIKE "%_surnames"
        OR cl_to LIKE "Lists_of_%"
      )
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      templatelinks
      JOIN linktarget ON lt_id = tl_target_id
    WHERE
      tl_from = article.page_id
      AND lt_namespace = 10
      AND (
        lt_title IN (
          "Wi",
          "Surname",
          "Given_name",
          "Animal_common_name",
          "Plant_common_name",
          "Nickname",
          "Dmbox"
        )
        OR lt_title LIKE "%index%"
        OR lt_title LIKE "%redirect%"
      )
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      categorylinks
    WHERE
      talk.page_id = cl_from
      AND (
        cl_to LIKE "%WikiProject_%"
        OR cl_to LIKE "%-Class_%_articles"
        OR cl_to LIKE "%-importance_%_articles"
        OR cl_to LIKE "%-priority_%_articles"
        OR cl_to LIKE "Unassessed_%_articles"
      )
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      templatelinks
      JOIN linktarget ON lt_id = tl_target_id
    WHERE
      tl_from = talk.page_id
      AND lt_namespace = 10
      AND (lt_title LIKE "%WikiProject_%")
  )
  AND EXISTS ( 
    SELECT
      1
    FROM
      categorylinks
    WHERE
      article.page_id = cl_from
      AND (
        cl_to RLIKE "^[^_]+_(births|deaths)$"
        OR cl_to LIKE "%_people"
      )
  )
  AND NOT EXISTS ( 
    SELECT
      1
    FROM
      categorylinks
    WHERE
      article.page_id = cl_from
      AND (
        cl_to LIKE "%fiction%"
        OR cl_to LIKE "%character%"
        OR cl_to LIKE "%books%"
        OR cl_to LIKE "%films%"
        OR cl_to LIKE "%programs%"
                OR cl_to LIKE "%_deities"
                OR cl_to LIKE "%_gods"
                OR cl_to LIKE "%mythology%"
                OR cl_to LIKE "%mythological%"
                OR cl_to LIKE "%mythical%"
        OR cl_to LIKE "%Easton's_Bible_Dictionary"
        OR cl_to LIKE "%Bible_people"
        OR cl_to LIKE "Biblical_people"
        OR cl_to LIKE "Old_Testament_people"
        OR cl_to LIKE "%pejorative%"
        OR cl_to LIKE "%_slang" 
        OR cl_to LIKE "%slang_" 
         OR cl_to LIKE "%slur%"
        OR cl_to LIKE "%_memes"  
        OR cl_to LIKE "%_of_individual_people"
        OR cl_to LIKE "%_based_on_real_people"
      )
  )
  AND NOT article.page_title LIKE "%_people"

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...