Custom Sort Orders in Wordpress

(2013)

Found a neat little piece of MySQL syntax today that I didn't know about. The FIELD() function can be used to define custom sorting in the database, like this:

 SELECT * FROM posts
   WHERE ID IN (1,2,3,4,5)
   ORDER BY FIELD(ID,3,4,5,1,2)

Whether it is preferable to sort in the database or the application probably depends what you're using it for and how many rows you have to sort. I suspect this is slow and will fall apart for big sorting, but for small stuff it seems fine. I'm using it to define multiple sort orders for different groups of posts in Wordpress. Each group has about 10 posts. For example, suppose that on the homepage you want them sorted one way, and then on a category page you want them sorted a second way, and then on a single page you want them sorted a third way. This is a handy way to do it.

Here's how to use it with Wordpress:

// in functions.php, define a custom "posts_orderby" filter
function my_posts_orderby($orderby) {
    global $post;
    $post_ids = get_post_meta($post->ID, 'post_ids', TRUE);
    if(count( $post_ids ) == 0) {
        return $orderby;
    }
    return "FIELD(ID," . join(',', $post_ids) . ")";
}

// .. later on
// in some place that we need to sort the posts
global $post;
$post_ids = get_post_meta($post->ID, 'post_ids', TRUE);

// add the custom filter
add_filter('posts_orderby', 'my_posts_orderby');

// perform the query
$query = new WP_Query(array(
    'post__in' => $post_ids,
));

// remove the custom filter
remove_filter('posts_orderby', 'my_posts_orderby');

while($query->have_posts()) {
    // etc.. proceed as usual
}

Pretty easy to setup! My actual implementation is wrapped inside a plugin class, but I've stripped out the boilerplate here. Another way to do this would be sorting by meta_value, but this way feels cleaner for my purposes.