WordPress.org

Ready to get started?Download WordPress

Forums

sort posts by custom field IN BACKEND (28 posts)

  1. skarck
    Member
    Posted 4 years ago #

    hi everyone!

    I'm looking for a way to customize the order of the post-list in the admin section to show all posts under "edit posts" ordered by a custom field.

    what i know is that the query for that list is in wp-admin/includes/post.php in the function wp_edit_posts_query()

    there it tells
    wp("post_type=post&$post_status_q&posts_per_page=$posts_per_page&order=$order&orderby=$orderby");

    unfortunately it DOESN'T work to change the string to
    ...orderby=meta_value&meta_key=MyCustomField
    the function seems to be unable to pull the metadata, coz orderby=post_title works...

    any idea to sort by a custom field or how to pull the metadata into this query?

    thanks in advance!

  2. s_ha_dum
    Member
    Posted 4 years ago #

    You can use the posts_* filters to alter those queries, at least you used to be able to. I haven't tried it in 2.7 or 2.8. you'' need a good grasp of SQL syntax and you'll need to be careful that you only edit the query when and where you want or you'll get very peculiar results all over the place.

  3. skarck
    Member
    Posted 4 years ago #

    thanks, i'm going to figure out the posts_join filter to join the postmeta-table to the query...
    although i'm not sure this is really neccassary, still hoping for a simple PleaseQueryTheMetadata()-thing in the wp() function.

    seems to be a hard nut... :((

  4. Mark / t31os
    Moderator
    Posted 4 years ago #

    You'll need to join the meta table at a minimum, else you have no meta data to sort by..

    Should be possible with 2 filters, one to join the post meta, the other to change the orderby..

    It's not likely to get any more simple then that...

    I can help if necessary.. (or try at least).. :)

  5. skarck
    Member
    Posted 4 years ago #

    Thanks a lot for your reply!
    i tried (with the help of the posted link above), but no success:

    function event_join($join) {
    $join .= " LEFT JOIN " . $wpdb->postmeta . " ON " .
           $wpdb->posts . ".ID = " . $wpdb->postmeta .
           ".post_id WHERE " . $wpdb->postmeta . ".meta_key = 'MyKey'";
      return $join;
    }
    function event_order($orderby) {
       $orderby = "meta_value ASC";
      return $orderby;
    }
    add_filter('posts_join', 'event_join' );
    add_filter('posts_orderby', 'event_order' );

    i would appreciate any help...

  6. s_ha_dum
    Member
    Posted 4 years ago #

    You probably don't want to put that 'WHERE' clause in the join function because you'll create a conflict with other 'WHERE' clauses, essentially creating two where clauses in the query. You should be getting an error. You need to move the 'WHERE' to another function and hook to posts_where.

  7. Mark / t31os
    Moderator
    Posted 4 years ago #

    I think apljdi has a good point there..
    Use another function to hook onto posts_where.

    I'm going to see if i can make a working example though, simply to see how it's done, i'll post back how i get on.

  8. Mark / t31os
    Moderator
    Posted 4 years ago #

    Oh wow, i've just realised something..

    Query posts already has built in meta value support, you can do this with one simple filter...

    Going to see if i can find a simple way to add an additional drop-down box with meta values (keys even), so you can use it as a selective filter.

  9. s_ha_dum
    Member
    Posted 4 years ago #

    This joins on postmeta and another table I created. It filters the edit posts table according to a peculiar 'page assignment' scheme that I've never liked but which editorial (by which I mean the editor no longer with us) insisted upon. It also reorders the edit posts table and adds and formats a couple of custom columns.

  10. Mark / t31os
    Moderator
    Posted 4 years ago #

    I'll have a look at that, i was scratching my head wondering how (if i wanted) i would go about adding additional columns, i was thinking along the lines of rebuilding the array in the manage_posts_columns function, as you've done there, so it's interesting to see.

    I like how you've hooked onto load-edit, i'd have never thought of that..

    So far i've just used a filter on pre_get_posts.. this allows meta order by..

    I'm thinking a simple dropdown with meta keys combined with the order by would be far less code and very simple, i'll show what i mean when i've tested.

    I also imagine if query_posts already supports meta values then the query must be holding meta related information already, so i'm not sure the additional joins or db query is necessary. Of course i could be totally wrong and there's nothing wrong with what you posted, so i'm not putting down your above work.. ;) ..just wondering if i can do the same with a little less work/code/queries... :)

  11. MichaelH
    Member
    Posted 4 years ago #

    When you get a final set of code, might I also suggest you consider adding a ticket to trac and providing a patch so this might get added to core.

    See Reporting Bugs.

  12. Mark / t31os
    Moderator
    Posted 4 years ago #

    Thanks for the suggestion, i'd not personally vouch for my hacky attempts at adding sections into the admin area, i think westi(ryan, scribu etc..) and the lads do a far better job then i do.. but i'll happily post the code here, if anyone wants to use it or critise they are welcome...

    Running some code now, which does the meta sorting, i can give a clear example of how to do that, the problem though now is how to selectively choose when the meta sorting is applied, and i thinking a simple action hook should work (to add a drop-down), but i'm still playing... :)

    Best reason i've had to sit and play with code for a few days.. :)

  13. s_ha_dum
    Member
    Posted 4 years ago #

    I dislike the way you have to reorder the columns but it works I guess.

    I also imagine if query_posts already supports meta values then the query must be holding meta related information already, so i'm not sure the additional joins or db query is necessary.

    It was probably 2.5 when I built the first version of that and I can't remember why I decided to do it the way I did. I know it has been altered since but I kept the basic structure. I don't have an additional query though. I have a subquery but that grabs data from a table I created so it has to be there.

    I'm thinking a simple dropdown with meta keys combined with the order by would be far less code and very simple, i'll show what i mean when i've tested.

    Maybe so. I haven't been back through it for awhile and every time I do go back through something I realize I've done something stupid. In fact, since I wrote I haven't really thought much about it until this thread.

    i thinking a simple action hook should work (to add a drop-down)

    I hooked to 'restrict_manage_posts' to get a select drop-down.

  14. Mark / t31os
    Moderator
    Posted 4 years ago #

    lol, coincidence, i stumbled on restrict_manage_posts when checking the action reference....

    Just got a drop-down added in.. Drawback is that a query is needed to grab all the meta keys, but it then gives you the power to control which posts show by their meta key (once i've finished building it).

    Currently hoping this should be sufficient, but i'm not sure if it's going to work as a "catch all"... it at least avoids picking up all the unwanted data like _edit_lock, _edit_data and so on..

    $wpdb->get_col("SELECT DISTINCT meta_key FROM $wpdb->postmeta WHERE SUBSTRING(meta_key,1,1) != '_'" )

    Will post up full code once it all works and looks flush with everything else.. :)

  15. Mark / t31os
    Moderator
    Posted 4 years ago #

    Here's what i have so far, any suggestions or critique welcome.
    http://wordpress.pastebin.ca/1635665

    Place directly in theme's functions.php, provides a drop-down selection to grab posts from a given meta key.

    Not really what the original poster asked for, but most ofwhat i've written can be stripped away to just re-order the posts by meta value.

  16. s_ha_dum
    Member
    Posted 4 years ago #

    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.

  17. skarck
    Member
    Posted 4 years ago #

    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.

  18. Mark / t31os
    Moderator
    Posted 4 years ago #

    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.

  19. Mark / t31os
    Moderator
    Posted 4 years ago #

    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).

  20. seborgarsen
    Member
    Posted 4 years ago #

    Very smooth.

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

  21. bsieders
    Member
    Posted 4 years ago #

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

    Thanks, Balthazar

  22. nashwebdesign
    Member
    Posted 4 years ago #

    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!

  23. rachelreveley
    Member
    Posted 4 years ago #

    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.

  24. Ian
    Member
    Posted 4 years ago #

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

  25. earthmanweb
    Member
    Posted 4 years ago #

    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!!!

  26. developingElegance
    Member
    Posted 4 years ago #

    @t31os_

    I registered for the forum to thank you for this.

    So... THANKS!

  27. Katie
    Member
    Posted 3 years ago #

    @t31os_

    This code worked wonderfully :-D Thank you SO much!

  28. joshkersh
    Member
    Posted 3 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic