User:TweetsFactsAndQueries/Queries/UK Members of Parliament whose Commons categories are not yet in Category:Politicians of the United Kingdom
Originally posted on Twitter and on Mastodon.
# UK Members of Parliament whose Commons categories are not yet in [[Category:Politicians of the United Kingdom]]
SELECT ?item ?commonsCategory
WITH {
# UK Members of Parliament
SELECT DISTINCT ?item WHERE {
?item wdt:P31 wd:Q5;
wdt:P39/wdt:P279* wd:Q16707842.
}
} AS %items
WITH {
# their categories (via P373 or commonswiki sitelink, which gets a bit complicated)
SELECT ?item ?commonsCategory WHERE {
hint:SubQuery hint:optimizer "None". # we have to disable the optimizer for this subquery because it gets confused by the ?commonsCategoryStatementEn part
INCLUDE %items.
OPTIONAL {
?item wdt:P373 ?commonsCategoryStatement.
BIND(STRLANG(CONCAT("Category:", ?commonsCategoryStatement), "en") AS ?commonsCategoryStatementEn)
?commonsCategoryFromStatement schema:name ?commonsCategoryStatementEn;
schema:isPartOf <https://commons.wikimedia.org/>.
}
OPTIONAL {
?commonsCategoryFromSitelink schema:about ?item;
schema:isPartOf <https://commons.wikimedia.org/>.
FILTER(STRSTARTS(STR(?commonsCategoryFromSitelink), "https://commons.wikimedia.org/wiki/Category:"))
}
BIND(COALESCE(?commonsCategoryFromStatement, ?commonsCategoryFromSitelink) AS ?commonsCategory)
FILTER(BOUND(?commonsCategory))
}
} AS %itemsWithCommonsCategories
WHERE {
# check if Commons category is in [[Category:Politicians of the United Kingdom]], using https://www.mediawiki.org/wiki/Wikidata_Query_Service/Categories
INCLUDE %itemsWithCommonsCategories.
MINUS {
SERVICE <https://query.wikidata.org/bigdata/namespace/categories/sparql> {
SERVICE gas:service {
gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS";
gas:linkType mediawiki:isInCategory;
gas:traversalDirection "Reverse";
gas:in <https://commons.wikimedia.org/wiki/Category:Politicians_of_the_United_Kingdom>;
gas:out ?commonsCategory.
}
}
}
}