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
- 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
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
- 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
Category: MediaWiki :: Article: 385
