sort posts by custom field IN BACKEND

good job!
it’s a very handy and useful piece of code, really enhances the power of WP as a CMS.
didn’t know that code in the theme’s function.php can change the bahavior of the admin interface, very clean coded!

thanks a lot, think this should be published like a plugin or something similar.

Nice.

SELECT meta_key FROM wp_postmeta WHERE meta_key NOT LIKE ‘\_%’ GROUP BY meta_key executes faster than your version using ‘DISTINCT’ and ‘SUBSTRING’ but the difference is very small– 0.0214 vs 0.0267 on my machine. I think the big difference is in the ‘GROUP BY’ but five thousandths of a second isn’t much of a big difference. And I like using the ‘SUBSTRING’. I’ve never tried that and it looks handy.

Initially tried the NOT LIKE option, but had problems getting it to work, i see you’ve caught my error though, forgot to escape the underscore.

I’ll have a play around with the query, but like you said it may not be necessary if it’s for the sake of a few thousandths of a second.

Turned out to be a little easier then initially expected. I do still like the idea of adding in an extra column, as you did with your code, but i’m not sure if it’s wise. I’d imagine joining all a posts associated meta keys would just create more load time, for very little benefit, other then seeing a column with some meta keys.

Took some time to play around and add a little more..

Meta column, custom field data, etc… optional.. (defines at the top – let the user decide if they want it).

if(defined('SHOW_META_VALUES') || defined('META_ALT_QUERY') || defined('SHOW_META_MULTIPLE')) die('Sorry, we need these defines, you can\'t have them.');
// Whether to show a posts meta values when filtering by meta key
define('SHOW_META_VALUES',true); // Might possibly slow down load times if viewing lots of posts when active
// Option to use alternate query - ymmv, so use whichever works best
define('META_ALT_QUERY',false); // Set to true if you have problems with the standard query, this may help, it may not..
// When filtering by meta key, this sets whether to show all a posts meta values (for that key) or just a single one.
// NOTE: SHOW_META_VALUES must be set to true in order for this to do anything
define('SHOW_META_MULTIPLE',true); // Set to true for multiple or false for single

function add_meta_column_head($defaults) {
	$defaults['meta'] = ( isset($_GET['meta_key']) && $_GET['meta_key'] != 'All') ? esc_attr($_GET['meta_key']) : '';
	return $defaults;
}

function add_meta_column($column_name,$post_id) {
	$metakey = ( isset($_GET['meta_key']) && $_GET['meta_key'] != 'All') ? esc_attr($_GET['meta_key']) : '';
	if( $column_name == 'meta' && '' != $metakey )
	$val_num = 1;
	echo "\n \t";
	if( SHOW_META_MULTIPLE ) {
		$meta = '<p>' . $val_num++ . ': '. implode( '</p>' . "\n \t" .'<p>' . $val_num++ . ': ',  get_post_meta( $post_id , $metakey , false ) ) . '</p>' . "\n";
	}
	else {
		$meta = '<p>' . $val_num++ . ': '. get_post_meta( $post_id , $metakey , true ) . '</p>' . "\n";
	}
	echo $meta;
	return;
}

function wp_admin_filters($query) {
	global $pagenow;
	if( $query->is_admin && ( 'edit.php' == $pagenow ) ) { 

		$metakey =
			( isset($_GET['meta_key']) && $_GET['meta_key'] != 'All' )
				? esc_attr( $_GET['meta_key'] )
				: '';
		$sortorder =
			( isset($_GET['order']) && $_GET['order'] == 'asc' )
				? 'asc'
				: 'desc';

		if( '' != $metakey ) {
			if( SHOW_META_VALUES ) {
				add_filter( 'manage_posts_columns' , 'add_meta_column_head' );
				add_action( 'manage_posts_custom_column' , 'add_meta_column' , 2, 2);
			}
			$query->set( 'orderby' , 'meta_value' );
			$query->set( 'meta_key' , $metakey );
		}
		if( $sortorder != 'desc' ) {
			$query->set( 'order' , 'asc' );
		}
		else {
			$query->set( 'order' , 'desc' );
		}
	}
	return $query;
}
function wp_admin_filters_dropdowns() {
	global $wpdb;

	$select_meta = '';
	if( !META_ALT_QUERY ) {
		$meta_keys = $wpdb->get_col("SELECT DISTINCT meta_key FROM $wpdb->postmeta WHERE SUBSTRING(meta_key,1,1) != '_'" );
	}
	else {
		$meta_keys = $wpdb->get_col("SELECT meta_key FROM wp_postmeta WHERE meta_key NOT LIKE '\_%' GROUP BY meta_key" );
	}

	if( !empty( $meta_keys ) ) {
		$metakey = ( isset($_GET['meta_key']) && $_GET['meta_key'] != 'All') ? esc_attr($_GET['meta_key']) : '';
		$select_meta .= '<select name="meta_key" id="meta" class="postform">';
		$select_meta .= ( $metakey == ( 'All' || '' ) )
			? "\n \t".'<option selected="selected" value="All">' . __('View all meta') . ' &nbsp;&nbsp;</option>'
			: "\n \t".'<option value="All">' . __('View all meta') . ' &nbsp;&nbsp;</option>';
		foreach($meta_keys as $key => $meta_option) {
			$select_meta .= ( $metakey == $meta_option )
				? "\n \t".'<option selected="selected" value="'.$meta_option.'">'.$meta_option.'</option>'
				: "\n \t".'<option value="'.$meta_option.'">'.$meta_option.'</option>';
		}
		$select_meta .= "\n".'</select>'."\n";
	}
	echo $select_meta;

	$select_order = '<select name="order">';
	$select_order .= "\n \t".'<option value="">&nbsp; --- &nbsp;</option>';
	$select_order .= ( isset($_GET['order']) && $_GET['order'] == 'asc' )
		? "\n \t".'<option selected="selected" value="asc">'.__('Ascending').'</option>'
		: "\n \t".'<option value="asc">'.__('Ascending').'</option>';
	$select_order .= ( isset($_GET['order']) && $_GET['order'] == 'desc' )
		? "\n \t".'<option selected="selected" value="desc">'.__('Descending').'</option>'
		: "\n \t".'<option value="desc">'.__('Descending').'</option>';
	$select_order .= "\n".'</select>'."\n";

	echo $select_order;

	return;
}
add_filter('pre_get_posts', 'wp_admin_filters');
add_action('restrict_manage_posts', 'wp_admin_filters_dropdowns',2);

You can sort your posts ascending or descending.

Props to apljdi for the alternate query (added that in as an option).

Very smooth.

Anyone fancy a front-end version? Even one where admin can select which meta fields are shown?

yes please!! Seborgarsen do you have it?
Been looking all over for something like this.

Thanks, Balthazar

t31os_,

i’m using this in my admin to sort posts according to numerical value. my numbers aren’t quiet sorting correctly. for example, if i’m trying to sort these numbers ascending: 1,20,30,200 it puts them in this order:

1
20
200
30

anyway to remedy this? otherwise this is a very handy piece of code. Thanks!

nashwebdesign you have to prefix your numbers with zeros so

001
020
030
200

Computers dont understand that 1 is the same as 01 or 001 and sort by the first number they see.

Is there any way I could use this to sort it by date in ascending order?

yeah, t31os_ , this code is gold for me right now!

I ran into a conflict with another custom column I defined, so I changed it slightly to the following:

function add_meta_column($column_name,$post_id) {
	$metakey = ( isset($_GET['meta_key']) && $_GET['meta_key'] != 'All') ? esc_attr($_GET['meta_key']) : '';

if( $column_name == 'meta' && '' != $metakey ):  //note change here on 'if block'
		$val_num = 1;
		echo "\n \t";
		if( SHOW_META_MULTIPLE ) {
			$meta = '<p>' . $val_num++ . ': '. implode( '</p>' . "\n \t" .'<p>' . $val_num++ . ': ',  get_post_meta( $post_id , $metakey , false ) ) . '</p>' . "\n";
		}
		else {
			$meta = '<p>' . $val_num++ . ': '. get_post_meta( $post_id , $metakey , true ) . '</p>' . "\n";
		}
		echo $meta;
		return;
	endif;
}

Thanks so much for this!!!

@t31os_

I registered for the forum to thank you for this.

So… THANKS!

@t31os_

This code worked wonderfully ?? Thank you SO much!

can someone tell me where you put this code, thanks!