Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
nlwiki Articles without content category
by
Bdijkstra
This query is marked as a draft
This query has been published
by
Maria zaos
.
Pages without any categories or with only maintenance categories. Query breakdown: 1. articles = a list of recently edited 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 1-5 minutes to execute on nlwiki_p.
Toggle Highlighting
SQL
USE glwiki_p; SELECT page_title, rev_timestamp FROM ( # recent bewerkte artikelen die niet genomineerd zijn en ouder dan 24 uur SELECT DISTINCT page_id, page_title, rev_timestamp FROM page, revision 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)$' ) AND page_id=rev_page AND rev_parent_id=0 AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(rev_timestamp,'%Y%m%d%H%i%s'))>86400 ) articles WHERE 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$)' 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
.
Checking query status...