Harvest Affiliate Members

Fork of Affiliate members harvest on Meta-Wiki by DAlangi (WMF)

This query is marked as a draft This query has been published by JAnstee (WMF).

SQL

# avoid duplicate user names
SELECT DISTINCT pl_title AS "Affiliated User Name",
page_title AS "Affiliate Group"

FROM commons_p.pagelinks AS pl 

JOIN commons_p.page AS p
ON pl.pl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN
("Commons_Photographers_User_Group",)
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND pl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND pl_title NOT LIKE "%MediaWiki_message_delivery%"
# focus on the User: namespace
AND pl.pl_namespace = 2

SQL

Checking query status...