Wordpress - Sort on meta value but include posts that don't have one
Easy Peasy, just tested 2018, using in production currently.
$query->set( 'meta_query', array(
'relation' => 'OR',
array(
'key' => 'custom_meta_key',
'compare' => 'EXISTS'
),
array(
'key' => 'custom_meta_key',
'compare' => 'NOT EXISTS'
)
) );
$query->set( 'orderby', 'meta_value title' );
This checks for all items with and without the meta key, with no value specified. the meta query provides the key for the orderby reliably. It has been tested. I am not sure, however, how it will work when the meta query uses multiple keys.
Practical example
/**
* Modifies query before retrieving posts. Sets the
* `meta_query` and `orderby` param when no `orderby`
* param is set, (default ordering).
*
* @param WP_Query $query The full `WP_Query` object.
* @return void
*/
function example_post_ordering( $query ) {
// if not in wp-admin,
// and the query is the main query,
// and the query is not a singular query,
// and the query does not have an orderby param set...
// Note: check for post types, etc. here as desired.
if ( ! is_admin()
&& $query->is_main_query()
&& ! $query->is_singular()
&& empty( $query->get( 'orderby' ) ) ) {
// Setting just `meta_key` is not sufficient, as this
// will ignore posts that do not yet, or never will have
// a value for the specified key. This meta query will
// register the `meta_key` for ordering, but will not
// ignore those posts without a value for this key.
$query->set( 'meta_query', array(
'relation' => 'OR',
array(
'key' => 'custom_meta_key',
'compare' => 'EXISTS'
),
array(
'key' => 'custom_meta_key',
'compare' => 'NOT EXISTS'
)
) );
// Order by the meta value, then by the title if multiple
// posts share the same value for the provided meta key.
// Use `meta_value_num` if the meta values are numeric.
$query->set( 'orderby', 'meta_value title' );
}
}
add_action( 'pre_get_posts', 'example_post_ordering', 10 );
This will order posts by custom_meta_key by default, and will not ignore posts without a value for that key.
This method will return all of the posts including those with and without the requested meta_key, but it will do weird things when ordering.
add_action('pre_get_posts', 'my_stuff');
function my_stuff ($qry) {
$qry->set(
'meta_query',
array(
'relation' => 'OR', # Matches to this meta_query should be added to those matching the 'meta_key' query
array(
'key' => 'item_price',
'value' => 'bug #23268',
'compare' => 'NOT EXISTS'
)
)
);
$qry->set('orderby', 'meta_value date'); # Sorting works with meta_value as well as meta_value_num - I've tried both
$qry->set('order', 'ASC DESC');
$qry->set('meta_key', 'item_price');
}
I found this by fiddling around with all the different answers to this question and analyzing the generated SQL through trial and error. It seems that setting array('meta_query' => array('relation' => 'OR')) outputs an appropriate LEFT JOIN instead of INNER JOIN that is necessary to include posts missing the metadata. Specifying the NOT EXISTS prevents the WHERE clause from filtering out posts lacking the meta field. For this particular WP_Query, the generated SQL is (indentation/newlines added):
SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'item_price')
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (2) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND (wp_postmeta.meta_key = 'item_price'
-- Oh look, here we give SQL permission to choose a random
-- row from wp_postmeta when this particular post is missing
-- 'item_price':
OR mt1.post_id IS NULL )
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value,wp_posts.post_date DESC
LIMIT 0, 10
The result is a listing of all the posts with meta_value of item_price and those missing item_price. All of the posts with item_price will be ordered correctly relative to each other, but posts missing item_price will use some random other meta value (say, _edit_last which seems to be 1 quite often in my database or some other internal wordpress metadata that is completely arbitrary) for its wp_postmeta.meta_value in the ORDER BY clause. So, while this method is close and may appear to work for certain data, it is broken. So, all I can say is, if your item_price values happen to not conflict with the random meta fields MySQL chooses for the posts missing item_price, this might work fine for you. If all you need is a guarantee that your posts with item_price are correctly ordered relative to each other without regard for the ordering of other posts, it may be OK. But I think this is just a shortcoming in wordpress. Please correct me, I hope I’m wrong and there’s a way to address this ;-).
It seems that for the INNER JOIN wp_postmeta, MySQL is choosing a random row from among multiple postmeta rows associated with the post when the meta_key is missing from the given post. From an SQL perspective, we need to figure out how to tell wordpress to output ORDER BY mt1.meta_value. This column is properly NULL when our requested meta_key is missing, unlike wp_postmeta.meta_value. If we could do that, SQL would sort these NULL (missing entries) before any other value, giving us a well-defined order: first come all the posts missing the particular postmeta field, second come the posts having the field. But that is the whole problem: 'orderby' => 'meta_value' can only refer to 'meta_key' => 'item_price' and the unaliased wp_postmeta is always an INNER JOIN instead of ever a LEFT JOIN, meaning wp_postmeta.meta_value and wp_postmeta.meta_key can never be NULL.
So I guess I have to say that this isn’t possible with wordpress’s built-in WP_Query as it is now documented (in wordpress-3.9.1). Bother. So if you actually need this to work correctly, you probably need to hook into wordpress elsewhere and modify the generated SQL directly.
There's two possible solutions to this:
1. All posts have meta
The best solution I have found here is to give the rest of the posts/products an item price of 0. You can do this manually, or loop through all the posts and if the price is empty then update it.
To make this manageable in the future you can hook into save_post and give them a value when they are first added (only if it's blank).
2. Multiple Queries
You could run the first query as you're doing and store the IDs of the posts returned. You could then run another query for all posts and orderby date, excluding the IDs return from the first query.
You can then print out the two results separately order and you'll get the desired results.