Jump to content

User:Jonesey95/self-transcluded-templates/doc

From Wikipedia, the free encyclopedia

A report that lists templates transcluded on zero pages or only on their own pages.

Query code

[edit]
{{Database report
|sql         =
SELECT page_title AS 'Template',
       DATE_FORMAT(MIN(rev_timestamp), '%Y-%m-%d') AS 'First edit',
       DATE_FORMAT(MAX(rev_timestamp), '%Y-%m-%d') AS 'Latest edit',
       COUNT(DISTINCT rev_actor) AS 'Unique authors',
       COUNT(rev_id) AS 'Revisions'
FROM
(
  SELECT page_id,
         page_title
  FROM page AS template
  WHERE NOT EXISTS
    (
      SELECT 1
      FROM templatelinks
      JOIN linktarget ON lt_id = tl_target_id
      JOIN page AS transcluder ON transcluder.page_id = tl_from
      WHERE lt_namespace = template.page_namespace
        AND lt_title = template.page_title
        AND tl_from != template.page_id
        AND NOT (transcluder.page_namespace = 11 AND transcluder.page_title = template.page_title)
        AND NOT (transcluder.page_namespace = 10
                 AND LEFT(transcluder.page_title, LENGTH(template.page_title) + 1) = CONCAT(template.page_title, '/'))
        AND NOT (transcluder.page_namespace = 2
                 AND transcluder.page_title = 'I_am_RedoStone/sandbox')
      LIMIT 1
    )
    AND page_namespace = 10
    AND page_is_redirect = 0
    AND page_title NOT LIKE "Adminstats/%"
    AND page_title NOT LIKE "AfC\_%"
    AND page_title NOT LIKE "Cite\_doi/%"
    AND page_title NOT LIKE "Cite\_pmid/%"
    AND page_title NOT LIKE "Country\_data\_%"
    AND page_title NOT LIKE "Did\_you\_know\_nominations/%"
    AND page_title NOT LIKE "Editnotices/%"
    AND page_title NOT LIKE "PBB/%"
    AND page_title NOT LIKE "POTD\_caption/%"
    AND page_title NOT LIKE "POTD\_credit/%"
    AND page_title NOT LIKE "POTD\_protected/%"
    AND page_title NOT LIKE "Taxonomy/%"
    AND page_title NOT LIKE "TemplateStyles\_sandbox/%"
    AND page_title NOT LIKE "TFA\_title/%"
    AND page_title NOT LIKE "User\_%"
    AND page_title NOT LIKE "%/testcases"
    AND page_title NOT LIKE "%/sandbox"
    AND page_title NOT LIKE "%/rater-data.js"
    AND page_title NOT LIKE "%-stub"
    AND page_title NOT IN
      (
        SELECT page_title
        FROM page
        JOIN categorylinks ON page_id = cl_from
        WHERE cl_to IN
          (
            'All_redirects_for_discussion',
            'Computer_language_user_templates',
            'Deprecated_templates_kept_for_historical_reasons',
            'Inactive_project_pages',
            'Language_user_templates',
            'Level-zero_userbox_templates',
            'Pages_using_the_Graph_extension',
            'Parameter_shared_content_templates',
            'Template_disambiguation_pages',
            'Template_sandboxes',
            'Template_test_cases',
            'Templates_for_deletion',
            'Wikipedia_preloaded_templates',
            'Wikipedia_substituted_templates',
            'Wikipedia_templates_currently_being_merged_or_deleted',
            'Wikipedia_transclusionless_templates'
          )
          AND page_namespace = 10
      )
) AS unused_templates
STRAIGHT_JOIN revision ON rev_page = page_id
GROUP BY page_title;
|wikilinks   = 1:10 <!-- Link 1st column to template space -->
|excerpts    =
|comments    = <!--Report SQL copied and adapted from https://quarry.wmcloud.org/query/80588-->
|widths      = <!--2:9em, 3:15em  Set 2nd column width as 9em, 3rd column width to 15em -->
|interval    = 1 <!-- Update every day -->
|header_template = static row numbers
|table_class = wikitable sortable static-row-numbers static-row-header-text
|table_style = overflow-wrap: normal
|pagination  = 2500
|max_pages   = 
}}

Query information

[edit]

Excludes:

Retrieved from "https://en.wikipedia.org/w/index.php?title=User:Jonesey95/self-transcluded-templates/doc&oldid=1245031786"