sort posts by custom field IN BACKEND

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.

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… :((

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

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…

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.

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.

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.

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.

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… ??

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.

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

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.

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

Here’s what i have so far, any suggestions or critique welcome.
https://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.

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.