List Post Titles Sorted on Custom Fields

The sample code here shows how to sort on the values of two Custom Fields, ‘Business Category’, and ‘Subcategory’, to produce a listing like this:

Automotive

Auto Accessories

  • Acme Auto
  • Bleppo Auto Specialties

Auto Parts

  • Speedy Auto
  • Z’s Auto Parts

The approach is to use a custom query to select the posts and the two custom fields and specify the correct sort order.  The current category and subcategory are used to create control breaks for the proper styling.


<?php

// List posts by Business Category, Subcategory
$sql = "
SELECT p.*,m1.meta_value as category, m2.meta_value as subcategory
FROM $wpdb->posts p
JOIN $wpdb->postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'Business Category')
JOIN $wpdb->postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'Subcategory')
WHERE p.post_type = 'post'
   AND p.post_status = 'publish'
ORDER BY m1.meta_value, m2.meta_value, p.post_title
";
$all_posts = $wpdb->get_results($sql);
$curr_cat = '';
$curr_subcat = '';
$in_list = false;
foreach ($all_posts as $post) {
   setup_postdata($post);
   $this_cat = $post->category;
   $this_subcat = $post->subcategory;
   if ($this_cat != $curr_cat) {
      $curr_cat = $this_cat;
      if ($in_list) {
         echo "</ul>\n";
         $in_list = false;
      }
      echo "<h2>$curr_cat</h2>\n";
      $curr_subcat = '';
   }
   if ($this_subcat != $curr_subcat) {
      $curr_subcat = $this_subcat;
      if ($in_list) {
         echo "</ul>\n";
      }
      echo "<h3>$curr_subcat</h3>\n";
      echo "<ul>\n";
      $in_list = true;
   } ?>
   <li><a href="<?php echo get_permalink($postid); ?>" title="<?php the_title(); ?>"><?php the_title(); ?></a></li>
<?php }
if ($in_list) echo "</ul>\n";

?>

Leave a Reply

Your email address will not be published. Required fields are marked *

*