Orderby meta_value only returns posts that have existing meta_key

Question

I have the following wp_query:

$args = array(
    'post_type' => 'news',
    'orderby' => 'meta_key',
    'order' => 'ASC',
    'meta_key'=>'custom_author_name',
    'post_per_page'=>-1
);

$query = new WP_Query($args);

echo $query->found_posts;

echo = 10 results because there are only 10 news posts with a meta_key = custom_author_name. But there are hundreds of news posts that don't have a post_meta row with that specific meta_key. Please notice, that there is no meta_query involved. No meta_value is assigned, because I am only trying to sort the posts by meta_key, and not filter by meta_value.

Shouldn't orderby select all posts? and just order them?

If so why is the result filtered? If the meta_key is not found, why not just use an empty string, or a match all?

If not, why not?

If I enter a meta_key to every news post (even if it's an empty string) then I get the expected result. But that seems like a whole lot of table rows that don't need to be there.

Answer 1

As stated in @ambroseya's answer, its supposed to work like that. Once you declare a meta query, even if you aren't looking for a specific value, it will only query posts with that meta key declared. If you want to include all posts sort them by the meta key, use the following code:

$args = array(
    'post_type' => 'news',
    'orderby' => 'meta_value',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'OR',
        array( 
            'key'=>'custom_author_name',
            'compare' => 'EXISTS'           
        ),
        array( 
            'key'=>'custom_author_name',
            'compare' => 'NOT EXISTS'           
        )
    ),
    'posts_per_page'=>-1
);

$query = new WP_Query($args);

echo $query->found_posts;

What this does is use an advanced meta query that looks for posts that do and don't have that meta key declared. Since the one with EXISTS is first, when you sort by meta_value, it'll use the first query.

Answer 2

I tried applying @Manny Fleurmond's answer and like @Jake I couldn't get it to work even after correcting the typo that 'orderby' => 'meta_key' should be 'orderby' => 'meta_value'. (And for completeness it should be 'posts_per_page' not 'post_per_page' but that doesn't affect the issue being looked at.)

If you look at the SQL query actually generated by @Manny Fleurmond's answer (having corrected the typos) this is what you get:

SELECT   wp_{prefix}_posts.* FROM wp_{prefix}_posts
LEFT JOIN wp_{prefix}_postmeta ON (wp_{prefix}_posts.ID = wp_{prefix}_postmeta.post_id AND wp_{prefix}_postmeta.meta_key = 'custom_author_name' )
LEFT JOIN wp_{prefix}_postmeta AS mt1 ON ( wp_{prefix}_posts.ID = mt1.post_id )
WHERE 1=1  AND ( 
    wp_{prefix}_postmeta.post_id IS NULL 
    OR 
    mt1.meta_key = 'custom_author_name'
) AND wp_{prefix}_posts.post_type = 'news' AND
(wp_{prefix}_posts.post_status = 'publish' OR wp_{prefix}_posts.post_author = 1 AND wp_{prefix}_posts.post_status = 'private')
GROUP BY wp_{prefix}_posts.ID ORDER BY wp_{prefix}_postmeta.meta_value ASC

This illustrates the way WP is parsing the query vars: it's creating a table for each meta_query clause, then figuring out how to join them and what to order by. The ordering would work fine if you were only using a single clause with 'compare' => 'EXISTS', but joining the second 'compare' => 'NOT EXISTS' clause with OR (as we must) messes up the ordering. The result is that LEFT JOIN is used to join both the first clause / table and the second clause / table - and the way WP puts everything together means that the table created using 'compare' => 'EXISTS' is actually being populated with meta_values from ANY custom field, not just the 'custom_author_name' field we are interested in. So I think ordering by that clause / table will only give the desired results if the particular post_type of 'news' only has a single custom field.

The solution that worked for my situation was to order by the other clause / table - the NOT EXISTS one. Seemingly counter-intuitive I know, but because of the way WP parses the query vars it is this table where meta_value is populated only by the custom field we are after.

(The only way I figured this out was by running the equivalent of this query for my case:

SELECT   wp_{prefix}_posts.ID, wp_{prefix}_postmeta.meta_value, mt1.meta_value FROM wp_{prefix}_posts
LEFT JOIN wp_{prefix}_postmeta ON (wp_{prefix}_posts.ID = wp_{prefix}_postmeta.post_id AND wp_{prefix}_postmeta.meta_key = 'custom_author_name' )
LEFT JOIN wp_{prefix}_postmeta AS mt1 ON ( wp_{prefix}_posts.ID = mt1.post_id )
WHERE 1=1  AND ( 
    wp_{prefix}_postmeta.post_id IS NULL 
    OR 
    mt1.meta_key = 'custom_author_name'
) AND wp_{prefix}_posts.post_type = 'news' AND
(wp_{prefix}_posts.post_status = 'publish' OR wp_{prefix}_posts.post_author = 1 AND wp_{prefix}_posts.post_status = 'private')
ORDER BY wp_{prefix}_postmeta.meta_value ASC

All I've done is changed the columns being shown and removed the GROUP BY clause. This then showed me what was going on - that the postmeta.meta_value column was pulling in values from all meta_keys, while the mt1.meta_value column was pulling in only meta_values from the news custom field.)

The Solution

As @Manny Fleurmond says, it is the first clause that is used for the orderby, so the answer is just to swap the clauses round, giving this:

$args = array(
    'post_type' => 'news',
    'orderby' => 'meta_value',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'OR',
        array( 
            'key' => 'custom_author_name',
            'compare' => 'NOT EXISTS'           
        ),
        array( 
            'key' => 'custom_author_name',
            'compare' => 'EXISTS'           
        )
    ),
    'posts_per_page' => -1
);

$query = new WP_Query($args);

Alternatively you can make the clauses associative arrays and order by the corresponding key, like so:

$args = array(
    'post_type' => 'news',
    'orderby' => 'not_exists_clause',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'OR',
        'exists_clause' => array( 
            'key' => 'custom_author_name',
            'compare' => 'EXISTS'           
        ),
        'not_exists_clause' => array( 
            'key' => 'custom_author_name',
            'compare' => 'NOT EXISTS'           
        )
    ),
    'posts_per_page' => -1
);

$query = new WP_Query($args);

Answer 3

That's actually how it works.

If you want to do that without adding table rows, you'll have to do two queries. One with the meta_key that has the limited results, and the other that gets the whole list; then use PHP to compare the two query results (possibly removing the meta_key results from the other query to remove duplicates, or whatever makes sense in your setting).

Answer 4

Unfortunately, that isn't how WP_Query works. As soon as you add that "meta" component, you've created a kind of filter. Dump $query->request and you will see what I mean.

Second, WP_Query doesn't support ordering by a meta key at all. You can order by a meta value for a particular key but no by the key itself. Again, dump the query to see what I mean. You will notice that the "order" components drop out if you try.

The cleanest way to get this to work, in my opinion, is a couple of short filters:

function join_meta_wpse_188287($join) {
  remove_filter('posts_join','join_meta_wpse_188287');
  global $wpdb;
  return ' INNER JOIN '.$wpdb->postmeta.' ON ('.$wpdb->posts.'.ID = '.$wpdb->postmeta.'.post_id)';
}
add_filter('posts_join','join_meta_wpse_188287');

function orderby_meta_wpse_188287($orderby) {
  remove_filter('posts_orderby','orderby_meta_wpse_188287');
  global $wpdb;
  return $wpdb->postmeta.'.meta_key ASC';
}
add_filter('posts_orderby','orderby_meta_wpse_188287');

$args = array(
    'post_type' => 'news',
    'post_per_page'=>-1
);
$q = new WP_Query($args);
var_dump($q->request); // debug
var_dump(wp_list_pluck($q->posts,'post_title')); // debug