Articles missing Navboxes

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

Articles which are linked to from a navbox template, but do not transclude said template.

Generates some false positives, such as Coord/speedtest, which I haven't found a workaround for.

SQL

/*SELECT pl_title FROM pagelinks
INNER JOIN page
ON page_id = pl_from
WHERE pl_from_namespace = 828 AND page_title = "Navbox"
Limit 50;*/

WITH tempTransesNav AS (SELECT page_id, lt_id FROM templatelinks
INNER JOIN linktarget
ON tl_target_id = lt_id
INNER JOIN page
ON tl_from = page_id
WHERE page_namespace = 10 AND lt_namespace = 10 AND lt_title = "Navbox"
                       LIMIT 1)
(SELECT * FROM pagelinks
INNER JOIN tempTransesNav
 ON pl_from = page_id
 Limit 50)
/*(SELECT tl_from FROM templatelinks
 INNER JOIN tempTransesNav
 ON lt_id = tl_target_id
 Limit 50);*/
 
/* tl_from FROM templatelinks tl WHERE (SELECT page_title FROM page WHERE page_id=tl.tl_target_id) = "Navbox"
LIMIT 50;*/
# select page titles and template titles
/*SELECT
page_title AS "Page Name",
(SELECT page_title FROM page WHERE page_id = pl_from) AS "Nav Template"
FROM page
JOIN pagelinks
ON pl_title = page_title
# with links incoming from templatespace
WHERE pl_from_namespace = 10
# and is in article space
AND page_namespace = 0
# and the incoming template is /probably/ an infobox template
#AND pl_from IN (SELECT tl_from FROM templatelinks tl INNER JOIN page p ON p.page_id = tl.tl_target_id WHERE p.page_title = "Navbox" AND tl_from = pl_from)
# and ensure the template isn't something transcluded into another template
#AND NOT EXISTS (SELECT tl_from FROM templatelinks WHERE tl_title = (SELECT page_title FROM page WHERE page_id = pl_from) AND tl_from_namespace = 10)
# check that the original page can "see" the Navbox, to work around <noinclude>ed stuff like /doc pages including navboxes
#AND page_id IN (SELECT tl_from FROM templatelinks WHERE tl_title = "Navbox" AND tl_from = page_id)
# and the page doesn't transclude the template
#AND (SELECT page_title FROM page WHERE page_id = pl_from) NOT IN (SELECT tl_title FROM templatelinks WHERE tl_from = page_id)
# and not a redirect
AND page_is_redirect = 0
LIMIT 200;*/

#SELECT page_title FROM page WHERE page_id IN (SELECT tl_from FROM templatelinks WHERE tl_title = "Navbox" AND tl_from_namespace = 10) LIMIT 50;

#SELECT (SELECT page_title FROM page WHERE page_id=tl_from) FROM templatelinks WHERE tl_title IN (SELECT page_title FROM page WHERE page_id IN (SELECT tl_from FROM templatelinks WHERE tl_title = "Navbox" AND tl_from_namespace = 10)) AND tl_from_namespace = 10 LIMIT 50;

#(SELECT tl_from FROM templatelinks WHERE tl_title = "Navbox" AND tl_from_namespace = 10 AND tl_namespace = 10)
#EXCEPT 
#(SELECT tl_from FROM templatelinks WHERE tl_title IN (SELECT page_title FROM page WHERE page_id IN (SELECT tl_from FROM templatelinks WHERE tl_title = "Navbox" AND tl_from_namespace = 10)) AND tl_from_namespace = 10) LIMIT 50;
#(SELECT page_title FROM page WHERE page_id = tl_from)

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