I’ve been modifying the built in WP search using the pre_get_posts
filter, allowing the user to sort the posts (including a bunch of custom post types) by different fields.
The problem I’m having though is that when I tell WP to sort by a meta value it will exclude all posts that don’t have that meta value set. This causes the number of results to change if you change sorting from say “Price” to “Date” because “Posts” don’t have “Price” set but “Items” do.
This is not what I want, so I’d like to know if there’s a way to include ALL posts – even those that lack the meta value I’m sorting on – and put the one’s without the value last.
I know how to sort on more than one field but that doesn’t help.
Thanks
Seems I’m not the only one with this question: Way to include posts both with & without certain meta_key in args for wp_query? but there’s no solution there.
Update
I’ve tried the answer but not sure if I understood correctly, here’s what I have right now:
<?php
function my_stuff ($qry) {
$qry->set('meta_query', array(array(
'key' => 'item_price',
'value' => '',
'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');
}
The meta value is a number (it is used to store a price as the name suggests)
Update 2
I’ve commented out the order-stuff and all I have now is this:
<?php
$qry->set('meta_query', array(array(
'key' => 'item_price',
'value' => '',
'compare' => 'NOT EXISTS'
)));
With this code the query seems to return all posts that don’t have the item_price
key and none of the posts that have it. I.E. the problem is now reversed.
If I add in the order-code as well I get 0 results.
Edit: …three years later… 😛 I had this issue again. I tried all the answers given and none work. Not sure why some people seem to think they work but they don’t work for me at least.
The solution I ended up with is using the save_post
filter – making sure all posts have the custom field I wish to sort on. It’s a bit annoying I have to do it, but at as long as you do it early on you’ll likely have no issues.
In this case I was building a “view counter” on posts and wanted users to be able to sort on the most read posts. Again, posts that have never been viewed (I guess that’s pretty unlikely – but still) disappeared when sorting on the view count. I added this bit of code to make sure all posts have a view count:
add_action('save_post', function ($postId) {
add_post_meta($postId, '_sleek_view_count', 0, true);
});
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.
Easy Peasy, just tested 2018, using in production currently.
Update 2022: Modified so that the
NOT_EXISTS
query comes before theEXISTS
query, and clarifies the effect that multiple keys has on theorderby
clause.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. The
orderby
clause’smeta_value
/meta_value_num
will use the last key in the chain.Practical example
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.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 appropriateLEFT JOIN
instead ofINNER JOIN
that is necessary to include posts missing the metadata. Specifying theNOT EXISTS
prevents theWHERE
clause from filtering out posts lacking the meta field. For this particularWP_Query
, the generated SQL is (indentation/newlines added):The result is a listing of all the posts with meta_value of
item_price
and those missingitem_price
. All of the posts withitem_price
will be ordered correctly relative to each other, but posts missingitem_price
will use some random other meta value (say,_edit_last
which seems to be1
quite often in my database or some other internal wordpress metadata that is completely arbitrary) for itswp_postmeta.meta_value
in theORDER 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 youritem_price
values happen to not conflict with the random meta fields MySQL chooses for the posts missingitem_price
, this might work fine for you. If all you need is a guarantee that your posts withitem_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 multiplepostmeta
rows associated with the post when themeta_key
is missing from the given post. From an SQL perspective, we need to figure out how to tell wordpress to outputORDER BY mt1.meta_value
. This column is properlyNULL
when our requestedmeta_key
is missing, unlikewp_postmeta.meta_value
. If we could do that, SQL would sort theseNULL
(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 unaliasedwp_postmeta
is always anINNER JOIN
instead of ever aLEFT JOIN
, meaningwp_postmeta.meta_value
andwp_postmeta.meta_key
can never beNULL
.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.The problem that everyone here is having has to do with the order of the meta queries. In order to sort correctly, you will need to put the “NOT EXISTS” query before the “EXISTS” query.
The reason for this is because WordPress uses the meta_value of the last “LEFT JOIN” statement in the “ORDER BY” clause.
For example:
I also encountered a similar problem and the following solution helped me:
I found a description on WordPress Codex with title “‘orderby’ with multiple ‘meta_key’s“: https://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters
I think I have a solution.
You can use two
meta_key
s, one that all posts have(like "_thumbnail_id")
, and themeta_key
you wish use as filter.So your args:
If suitable, you can add a default meta value each time a post is saved or updated, if the meta value does no exists.
If you are using a custom post type, replace the
add_action('save_post', 'addDefaultMetaValue');
byadd_action('save_post_{post_type}', 'addDefaultMetaValue');
e.g.add_action('save_post_product', 'addDefaultMetaValue');
I had the problem by myself for numeric meta values and pointed out that it the order of the query is also important. For me the
NOT EXISTS
query has to be the first one.Example:
Also important in order to get the right direction for numeric values is the general
’orderby’
to be set to’meta_value_num’
. Otherwise you have strange results for numeric values, e. g.:1, 2, 20, 21, 3, 4, 5 …
Instead of:
1, 2, 3, 4, 5 … 20, 21
This is a very old question but none of the answers were robust enough for me to use and be happy with the results. The solution below still uses
WP_Query
, but manipulates the join and order by statements.It makes use of a MySQL function called
ORDER BY IF
, to allow you to effectively run logic during your ORDER BY statement. This is important because the other solutions here just lump the results with no value either at the front or end of the list, which is useless when you’re trying to order by 2 keys.In the below example I have a CPT of ‘businesses’. I want them all ordered by title but any with
is_premium
should come first. I’ve commented the code so you can see what’s going on where.The OR meta query combining
NOT EXISTS
andEXISTS
works but it caused a slow query.This solution works a lot faster.
The main key was in the SQL order by clause:
order by YOUR_FIELD is NULL, YOUR_FIELD
And in your WP_Query args:
There’s a possible
orderby
value ofmeta_value
for that.If you got numeric values, just use
meta_value_num
instead.Disclaimer: This is not tested, but it should work. Point is that you need to specify your
meta_key
andkey
values. Else you can’t compare against non-existing values, which should make it possible to query both sorts of posts. It’s some kind of hack-ish, but as long as it works…I think what @kaiser was trying to do was to tell the query to return all posts that have that meta key by applying a sort of dummy where condition to not filter any of those posts. So if you know all the values your custom fields can take are x,y,z you could say “WHERE meta_key IN(x,y,z)” but the idea is you can avoid that problem all together by saying != (”):
Also not tested but feels like it’s worth a try :-).
I ended up getting around this with a bit of a hack (IMHO), but it did the job for me in my case.
You can hook into the filters posts_join_paged and posts_orderby to update the join and order strings. This will allow you to order by whatever you want as long as you join it first rather than WP_Query assuming that the field has to exist for that particular post. You can then remove the
meta_key
,orderby
, and `order from your WP_Query args.Below is an example. At the top of each function I had to escape out for certain cases since it will add this to everything that uses WP_Query. You may need to modify that to fit your particular needs.
Documentation on these two filters is sadly lacking so… good luck! 🙂
This solution worked for me:
However, this solution first shows the records with null meta_value. This other solution shows ASC order and nulls at the end:
I was able to solve this by using one query and proper args. WP 4.1+
See my full answer here: https://wordpress.stackexchange.com/a/370841/15209
I ended up tapping into the ‘get_meta_sql’ filter that WordPress provides
The function looks like this
It solves two problems with the WordPress generated sql.
Your particular query may be different, so be sure to test and adjust as necessary.
Folks, thank you for all the valid answers but, right now (2022), nesting is the key:
Bonus case: I didn’t test this but if the meta is not a 0/1 value, the third rule should be: