List available pages for a user

[jtullett]

This is a pretty ugly piece of hackery, so I'm hoping there's some sort of award.

I had a request from a user for a page to list other pages he was authorized to use. So I came up with this:

Code:

if ($user->data()->id) {
    $pageQuery = "SELECT DISTINCT `pages`.`page`, `pages`.`title` FROM `user_permission_matches` RIGHT JOIN `users` on `user_permission_matches`.`user_id`=`users`.`id` RIGHT JOIN `permission_page_matches` ON `user_permission_matches`.`permission_id`=`permission_page_matches`.`permission_id` RIGHT JOIN `pages` ON `pages`.`id`=`permission_page_matches`.`page_id` WHERE `users`.`id`= ? AND `title` LIKE 'PROJECT-PREFIX %'";
    $db->query($pageQuery, [$user->data()->id]);
    foreach ($db->results() as $page) {
        // Build a piece of HTML out of $page->page and $page->title
        // For bonus nastiness I check for <pagename>.png to use as an icon. Fugly but consistent ftw.
    }
}

Each page title already has a project prefix, so basically I'm just asking for a list of pages with that title, and matching uid->permission. There's no sorting, which may or may not be a problem at some stage.

I'm sure there's a much more elegant way, so suggestions are welcome!