List MediaWiki Articles

What do I want?
Page ID, Title, Content, Category, AccessLevel (note that AccessLevel is extra to your standard MediaWiki setup and one that was customized for my day job).

Thought I already had this somewhere on my site, so it took a while again but I've posted my finished query here:

The base query to list mediawiki articles
Page ID, Title, Content, Category
copyraw
SELECT
		p.page_id AS PageID,
		CONVERT(p.page_title USING latin1) AS PageTitle,
		CONVERT(t.old_text USING latin1) AS PageContent,
		(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory
	FROM
		wikimedia_page p
	LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
	LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
	WHERE
		p.page_namespace=0
		AND t.old_text=''
		AND p.page_is_redirect=0
  1.  SELECT 
  2.          p.page_id AS PageID, 
  3.          CONVERT(p.page_title USING latin1) AS PageTitle, 
  4.          CONVERT(t.old_text USING latin1) AS PageContent, 
  5.          (SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory 
  6.      FROM 
  7.          wikimedia_page p 
  8.      LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id 
  9.      LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id 
  10.      WHERE 
  11.          p.page_namespace=0 
  12.          AND t.old_text='' 
  13.          AND p.page_is_redirect=0 


My custom query
To list access levels (this needs to check categories with access levels table), I have a separate table containing the wiki categories associated with local wiki groups (required to view the category): Page ID, Title, Content, Category, Groups
copyraw
SELECT
	p.page_id AS PageID,
	CONVERT(p.page_title USING latin1) AS PageTitle,
	CONVERT(t.old_text USING latin1) AS PageContent,
	(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory,
	(SELECT GROUP_CONCAT(TRIM(REPLACE(ccg.GroupName_Friendly, '_', ' '))) FROM custombu_category_restrictions ccr INNER JOIN custombu_category_groups ccg ON ccg.BUKBGroupID=ccr.GroupAllowedID WHERE REPLACE(ccr.CategoryName, ' ', '_') IN (SELECT CONVERT(wikimedia_categorylinks.cl_to USING latin1) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id)) AS GroupsAccess
FROM
	wikimedia_page p
LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
WHERE
	p.page_namespace=0
	AND t.old_text=''
	AND p.page_is_redirect=0
  1.  SELECT 
  2.      p.page_id AS PageID, 
  3.      CONVERT(p.page_title USING latin1) AS PageTitle, 
  4.      CONVERT(t.old_text USING latin1) AS PageContent, 
  5.      (SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory, 
  6.      (SELECT GROUP_CONCAT(TRIM(REPLACE(ccg.GroupName_Friendly, '_', ' '))) FROM custombu_category_restrictions ccr INNER JOIN custombu_category_groups ccg ON ccg.BUKBGroupID=ccr.GroupAllowedID WHERE REPLACE(ccr.CategoryName, ' ', '_') IN (SELECT CONVERT(wikimedia_categorylinks.cl_to USING latin1) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id)) AS GroupsAccess 
  7.  FROM 
  8.      wikimedia_page p 
  9.  LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id 
  10.  LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id 
  11.  WHERE 
  12.      p.page_namespace=0 
  13.      AND t.old_text='' 
  14.      AND p.page_is_redirect=0 
Category: MediaWiki :: Article: 385

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Please publish modules in offcanvas position.