Recently for work I needed to create a custom post type that had a few custom columns visible in the WordPress admin. The custom columns needed to be sortable and I had three different types of custom columns: custom post meta, custom taxonomy, and custom author meta.

I googled and tried and googled some more and even bing’ed. Finding solutions to sort by custom post meta was numerous, but I couldn’t find any on how to sort by custom author meta. After I decided to look for how to sort by taxonomy instead, I took what I found there and modified it to sort by author meta. And so, here goes….

Note: code tested and works with WordPress 3.4.1, with Multisite enabled.

My post type is ‘feedingprograms’, custom taxonomy is ‘fp_status’, post meta is ‘fp_first_del_date’, and custom author meta is ‘church_name’.

As a quick overview, here is my code to create the custom columns:

add_filter("manage_edit-feedingprograms_columns", "feedingprograms_columns");
function feedingprograms_columns($columns) {
	$columns = array(
		"cb" => "",
		"title" => "Feeding Program Name",
		"church" => "Church",
		"1st_del_dt" => "First Delivery Date",
		"status" => "Status",
		"date" => "Date"
	);
	return $columns;
}

And the code to make the columns sortable:

function feedingprograms_enable_sortable($columns) {
	$columns['church'] = 'church';
	$columns['status'] = 'status';
	$columns['1st_del_dt'] = '1st_del_dt';
	return $columns;
}
add_filter('manage_edit-feedingprograms_sortable_columns','feedingprograms_enable_sortable');

I’ll skip the code to show how to fill the contents of the columns as there are many tutorials out there for that.

Ok, now for the meat of this post. Now we needed to instruct WordPress how to sort these sortable columns. First of all, add a filter to edit the queries before they run. I wanted it to only run within the admin, hence the is_admin condition

if(is_admin()) {
	add_filter( 'pre_get_posts', 'admin_cpt_queries' );
}

And the function that runs the filter. Note: this project will eventually have many other post types, so I prepped my code for the future

function admin_cpt_queries($query) {
	switch ($query->query_vars['post_type']) {
		case 'feedingprograms'://what to do when this post type is viewed
			add_filter('posts_clauses', 'feedingprograms_a_query',10,2);
			if('1st_del_dt'==$query->get('orderby')) {//instructions to sort by post meta here
				$query->set('meta_key','fp_first_del_date');
				$query->set('orderby','meta_value_num');
			}
			break;
		//add other cases for your other post types here
		default:
			break;
    }
    return $query;
}

So when ‘feedingprograms’ are viewed on the admin, the feedingprograms_a_query function will be called

function feedingprograms_a_query($posts_clauses, $query) {
	global $wpdb;
	if(isset($query->query['orderby'])) {//check if a sort was requested
		if($query->query['orderby']=='church') { //custom author meta column sort - check if the sort requested was for this column
			$posts_clauses['join'].= " left outer join $wpdb->usermeta ON $wpdb->posts.post_author = $wpdb->usermeta.user_id";
			$posts_clauses['where'].= " AND $wpdb->usermeta.meta_key = 'church_name'";
			$posts_clauses['orderby']="$wpdb->usermeta.meta_value ".($query->query['order']=='asc' ? "asc" : "desc");
			//custom SQL to modify the query. Modify as needed for your needs
		}elseif($query->query['orderby']=='status') { //custom taxonomy column sort
			$posts_clauses['join'] .= "
			LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
			LEFT OUTER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id AND {$wpdb->term_taxonomy}.taxonomy='fp_status'
			LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
";
			$posts_clauses['groupby'] = "$wpdb->posts.ID";
			$posts_clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
			$posts_clauses['orderby'] .= ( 'ASC' == strtoupper( $query->get('order') ) ) ? 'ASC' : 'DESC';
			//same idea, sort, custom SQL to sort by custom tax
		}elseif($query->query['orderby']=='1st_del_dt') { //post meta column sort
			$posts_clauses['orderby']="$wpdb->postmeta.meta_value ".($query->query['order']=='asc' ? "asc" : "desc");			
			//modify the sort order here. SQL covered by $query in previous function - easier than manually writing the query here
		}
	}
	return $posts_clauses;
}

And there it is. Hope this is helpful for you.

My references:
Sort by custom taxonony
Add a switch for each custom post type

Note: It’s been awhile since I blogged. I’ve lost so many good code snippets by forgetting to blog about them. So this post was written pretty much as soon as I figured it out.

Photo by NCinDC (cc), cropped and resized.