Luke Carbis It’s more complicated than 42.

How to order by post meta item without setting meta_key in WordPress

Problem: I wanted wp_query to sort by a custom meta item, but I also wanted it to include results that don't have that meta item assigned to them! When I use the built in 'orderby => 'meta_value', it requires me to set 'meta_key' => 'something' - and that won't include results that don't have that meta_key assigned.

Solution: I found some query filters that let me edit the SQL of the query, after all my other arguments have been considered. This means I can use an INNER JOIN and a different ORDER BY value to achieve the result I'm after. Here's the poetry:

Source code

<?php
add_filter( 'posts_clauses', 'my_custom_order_function', 20, 1 );
function my_custom_order_function( $pieces ) {
 
    $pieces['join'] .= " INNER JOIN (SELECT meta_id, post_id, meta_key, meta_value FROM wp_postmeta ORDER BY CASE meta_key WHEN 'custom_meta' THEN 1 ELSE 2 END ASC, meta_value ASC) AS cm1 ON (wp_posts.ID = cm1.post_id)";
 
    $pieces['orderby'] = "CASE cm1.meta_key WHEN 'custom_meta' THEN 1 ELSE 2 END, cm1.meta_value ASC, wp_posts.post_title ASC";
 
    return $pieces;
 
}
?>

Okay, so basically what we're doing here as adding a second postmeta table (the first is already added so that wp_query can handle meta queries), but before it's joined, we order it using MySQL's CASE order. This puts the meta_key we're looking for at the top of the JOIN, so if it exists, it will be that meta_key that is linked to our results.

Once we have joined the table, we just need to order by the join - again we do this using CASE, to put the results in the right order - items with our meta_key go first, and everything else second. We also do a secondary ORDER so that the items with the right meta_key also get ordered by meta_value.

It's important that you only use this code in context - pasting it in as is will force it to apply to every single query that is run on each page - and you probably don't want that.

If anyone can suggest a better way of doing it, or if there's something I missed, please let me know in the comments.