Creating a Page Loop with a Custom Select Query

I hit a roadblock today in one of my projects, it requires a page template that would display a list of pages. Listing posts based on a category is basic with query_posts but I needed to do the same thing for pages. I ended up posting a tweet and Dean responded shortly after with a link to the WordPress codex explaining the custom select query (I love the -web-).

After reading though the doc I was able to create this:

<?php
$querystr = ”
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key = ‘repertory’
    AND wposts.post_type = ‘page’
    ORDER BY wpostmeta.meta_value DESC
 ”;
 $pageposts = $wpdb->get_results($querystr, OBJECT);
?>
 <?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

I’ll break it down just a little, you can read the doc if you’d like to learn more.

AND wpostmeta.meta_key = 'page-category' 

“page-category” is the key that I’ve assigned to the page. This is creating a category for your pages. I don’t recommend creating these false categories if unnecessary, use tags, I’m using these because I need to sort the pages based on the value.

AND wposts.post_type = 'page' 

Basically, only display pages.

ORDER BY wpostmeta.meta_value DESC

Finally, sort the pages based on the key value.