nlwiki Articles without content category

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

Pages without any categories or with only maintenance categories.

Query breakdown:
1. outer query = a list of article pages that are not nominated for deletion and older than 24 hours
2. hc = a list of maintenance categories, meaning the titles of category pages with the __HIDDENCAT__ or __EXPECTUNUSEDCATEGORY__ property
    note: could filter out empty categories using the `category` table
3. nhcl = take `categorylinks`, filter out hc and collapse to a single row per page
4. final result = get the titles of article pages not present in nhcl, and the creation timestamp

Takes about 30s to execute on nlwiki.

SQL

SELECT page_title, rev_timestamp
FROM page INNER JOIN revision ON (
	rev_parent_id=0 AND page_id=rev_page AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(rev_timestamp,'%Y%m%d%H%i%s'))>86400)
WHERE 
  	page_namespace=0 AND 
  	page_is_redirect=0 AND
  	NOT EXISTS (SELECT * FROM page_props WHERE page_id=pp_page AND pp_propname='disambiguation') AND
  	NOT EXISTS (
        SELECT * 
      	FROM categorylinks 
      	WHERE page_id=cl_from AND cl_to REGEXP '^Wikipedia:(Verwijderbaar/[0-9]{2}|Nog_niet_gereed|Nuweg|Werk_in_uitvoering)$'
    ) AND
  	NOT EXISTS (SELECT * FROM (
		# niet-beheer categorylinks
		SELECT cl_from, cl_to FROM categorylinks WHERE 
      		cl_to NOT REGEXP '^(Diersoort_beschreven_in_|Actuele_(sport)?gebeurtenis$|^Wikipedia:Incomplete_lijsten)' AND
            NOT EXISTS (SELECT * FROM (
                # beheercategorieën
                SELECT DISTINCT page_title
                FROM page, page_props
				WHERE page_namespace=14 AND page_id=pp_page AND (pp_propname='hiddencat' OR pp_propname='expectunusedcategory')
            ) hc WHERE cl_to=hc.page_title)
		GROUP BY cl_from
		ORDER BY cl_from
	) nhcl WHERE nhcl.cl_from=page_id)
ORDER BY rev_timestamp

/*SELECT page_title, rev_timestamp
FROM page 
	INNER JOIN (SELECT DISTINCT rc_cur_id FROM recentchanges WHERE rc_type=0 AND rc_namespace=0) rc ON rc_cur_id=page_id
	INNER JOIN revision ON (
		rev_parent_id=0 AND page_id=rev_page AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(rev_timestamp,'%Y%m%d%H%i%s'))>86400)
WHERE 
  	#page_namespace=0 AND 
  	page_is_redirect=0 AND
  	NOT EXISTS (SELECT * FROM page_props WHERE page_id=pp_page AND pp_propname='disambiguation') AND
  	NOT EXISTS (
        SELECT * 
      	FROM categorylinks 
      	WHERE page_id=cl_from AND cl_to REGEXP '^Wikipedia:(Verwijderbaar/[0-9]{2}|Nog_niet_gereed|Nuweg|Werk_in_uitvoering)$'
    ) AND
  	NOT EXISTS (SELECT * FROM (
		# niet-beheer categorylinks
		SELECT cl_from, cl_to FROM categorylinks WHERE 
      		cl_to NOT REGEXP '^(Diersoort_beschreven_in_|Actuele_(sport)?gebeurtenis$|^Wikipedia:Incomplete_lijsten)' AND
            NOT EXISTS (SELECT * FROM (
                # beheercategorieën
                SELECT DISTINCT page_title
                FROM page, page_props
				WHERE page_namespace=14 AND page_id=pp_page AND (pp_propname='hiddencat' OR pp_propname='expectunusedcategory')
            ) hc WHERE cl_to=hc.page_title)
		GROUP BY cl_from
		ORDER BY cl_from
	) nhcl WHERE nhcl.cl_from=page_id)
ORDER BY rev_timestamp*/

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