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

	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

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

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

< Prev		Next >