How to sort the admin area of a WordPress custom post type by a custom field

When editing one of my custom post types I want to be able to list all entries by a custom field instead of the date they are published (which, for a custom post type probably isn’t relevant). I got a lead from the comments of a blog post about custom post types and the author said it was possible and that he even made it so you could click on the column names for a custom sort. He mentioned the posts_orderby function which I noted in my own comments but now I can find the blog post anymore. Any suggestions? I saw one solution that used

add_action('wp', 'check_page');

And the check_page function used add_filter to change the query but I’m pretty sure it would only work in the theme files, not in the admin area.

As you can probably imagine by the lack of answers provided, the solution is not exactly trivial. What I’ve done is create a somewhat self-contained example that assumes a custom post type of “movie” and custom field key of “Genre“.

Disclaimer: this works with WP3.0 but I can’t be sure it will work with earlier versions.

You basically need to hook two (2) hooks to make it work and another two (2) to make it obvious and useful.

The first hook is ‘restrict_manage_posts‘ which lets you emit an HTML <select> in the area above the list of posts where the “Bulk Actions” and “Show Dates” filters. The code provided will generate the “Sort by:” functionality as seen in this screen snippet:

_{(source: mikeschinkel.com)}

The code uses direct SQL because there is not a WordPress API function to provide the list of all meta_keys for a post types (sounds like a future trac ticket to me…) Anyway, here’s the code. Note that it grabs the post type from $_GET and validates to make sure it is both a valid post type post_type_exists() as well as being a movie post type (those two checks are overkill but I did it to show you how if you don’t want to hard-code the post type.) Lastly I use the sortby URL parameter as it doesn’t conflict with anything else in WordPress:

add_action('restrict_manage_posts','restrict_manage_movie_sort_by_genre');
function restrict_manage_movie_sort_by_genre() {
    if (isset($_GET['post_type'])) {
        $post_type = $_GET['post_type'];
        if (post_type_exists($post_type) && $post_type=='movie') {
            global $wpdb;
            $sql=<<<SQL
SELECT pm.meta_key FROM {$wpdb->postmeta} pm
INNER JOIN {$wpdb->posts} p ON p.ID=pm.post_id
WHERE p.post_type="movie" AND pm.meta_key='Genre'
GROUP BY pm.meta_key
ORDER BY pm.meta_key
SQL;
            $results = $wpdb->get_results($sql);
            $html = array();
            $html[] = "<select id=\"sortby\" name=\"sortby\">";
            $html[] = "<option value=\"None\">No Sort</option>";
            $this_sort = $_GET['sortby'];
            foreach($results as $meta_key) {
                $default = ($this_sort==$meta_key->meta_key ? ' selected="selected"' : '');
                $value = esc_attr($meta_key->meta_key);
                $html[] = "<option value=\"{$meta_key->meta_key}\"$default>{$value}</option>";
            }
            $html[] = "</select>";
            echo "Sort by: " . implode("\n",$html);
        }
    }
}

The second required step is to use the parse_query hook that is called after WordPress decides one what query is should run but before it runs the query. Here we get to set values of orderby and meta_key in the query’s query_var array which are documented in the Codex in the orderby parameter for query_posts(). We test to make sure that:

1. We are in the admin (is_admin()),
2. We are on the page that lists posts in the admin ($pagenow=='edit.php'),
3. The page has been called with a post_type URL parameter equal to movie, and
4. The page has also been called with a sortby URL parameter and that it wasn’t passed a value of ‘None‘

If all those tests pass we then set the query_vars (as documented here) to meta_value and our sortby value for ‘Genre‘:

add_filter( 'parse_query', 'sort_movie_by_meta_value' );
function sort_movie_by_meta_value($query) {
    global $pagenow;
    if (is_admin() && $pagenow=='edit.php' &&
        isset($_GET['post_type']) && $_GET['post_type']=='movie' && 
        isset($_GET['sortby'])  && $_GET['sortby'] !='None')  {
        $query->query_vars['orderby'] = 'meta_value';
        $query->query_vars['meta_key'] = $_GET['sortby'];
    }
}

And that’s all you need to do; no “posts_order” or “wp” hooks required! Of course you actually do need to do more; you need to add some columns on your page that lists the posts so you can actually see the values that it is sorting by otherwise the users will get mucho confused. So add a manage_{$post_type}_posts_columns hook, in this case manage_movie_posts_columns. This hook gets passed the default array of columns and for simplicity I just replaced it with two standard columns; a checkbox (cb) and a post name (title). (You can inspect posts_columns with a print_r() to see what else is available by default.)

I decided to add a “Sorted By:” for when there is a sortby URL parameter and when it is not None:

add_action('manage_movie_posts_columns', 'manage_movie_posts_columns');
function manage_movie_posts_columns($posts_columns) {
    $posts_columns = array(
        'cb' => $posts_columns['cb'],
        'title' => 'Movie Name',
        );
    if (isset($_GET['sortby']) && $_GET['sortby'] !='None') 
        $posts_columns['meta_value'] = 'Sorted By';

    return $posts_columns;
}

Finally we use the manage_pages_custom_column hook to actually display the value when there is a post of the appropriate post type and with the probably redundant test for is_admin() and $pagenow=='edit.php'. When there is a sortby URL parameter we extract the custom field value that is being sorted by an display it in our list. Here’s what it looks like (remember, this is test data so no comments from the peanut gallery on the movie classifications! :):

_{(source: mikeschinkel.com)}

And here is the code:

add_action('manage_pages_custom_column', 'manage_movie_pages_custom_column',10,2);
function manage_movie_pages_custom_column($column_name,$post_id) {
    global $pagenow;
    $post = get_post($post_id);
    if ($post->post_type=='movie' && is_admin() && $pagenow=='edit.php')  {
        switch ($column_name) {
            case 'meta_value':
                if (isset($_GET['sortby']) && $_GET['sortby'] !='None') {
                    echo get_post_meta($post_id,$_GET['sortby'],true);
                }
                break;
        }
    }
}

Note that this only picks up the first “Genre” for a movie, i.e. the first meta_value in the case of multiple values for a given key. But then again I’m not sure how it would work otherwise!

And for those unfamiliar with where to put this code you can put it in a plugin or more likely for the newbie in the functions.php file in your current theme.

How this helps.