Order a query result by a numeric meta key even if it does not exist, but put the posts with meta key first

Question

I want to order a WP Query result by a numeric meta value in PHP.

To achieve this, I am doing as follows:

$query->set( 'meta_query', array(
    'relation' => 'OR',
    array(
        'key' => 'orden_en_categ', 
        'compare' => 'EXISTS',
        'type'    => 'NUMERIC'
    ),
    array(
        'key' => 'orden_en_categ', 
        'compare' => 'NOT EXISTS',
        'type'    => 'NUMERIC'
    )
) );
$query->set('orderby', 'meta_value_num' );
$query->set('order', 'ASC');

Where the meta key is called orden_en_categ.

And what is the problem?

This query setting is displaying posts in the following order:

1. Post without meta
2. Post without meta
3. Post with meta and low values
4. Post with meta and higher values
5. ...

I want the posts without meta appear after the posts with meta, as follows:

1. Post with meta and low values
2. Post with meta and higher values
3. Post without meta
4. Post without meta
5. ...

Where the order of the posts without meta does not matter.

The default meta solution:

I know I could add a default meta to all posts with a value of something like 999 and this would work. But, there must be an alternative way, without having to add those sticky meta values.

Answer 1

First of all, just to clarify, in a normal situation without the specific order requirements, and assuming you are doing this on pre_get_posts hook, you would just do as follows:

add_action('pre_get_posts', function ($query) {
    // only perform for a given query
    // do your checks and return early
    if (! $query->is_main_query() || ! $query->is_home()) {
        return;
    }

    $query->set('meta_key', 'orden_en_categ');
    $query->set('orderby', 'meta_value_num');
    $query->set('order', 'ASC');
});

There is no need to set a meta query at all.

Then, to achieve an ascending order with nulls last, there are differents approaches. Assuming your data is numeric, you could use a minus operator and order descending as follows:

add_filter('posts_orderby', function ($orderby, $query) {
    global $wpdb;

    // only perform for a given query
    // do your checks and return early
    if (! $query->is_main_query() || ! $query->is_home()) {
        return $orderby;
    }

    return "-{$wpdb->postmeta}.meta_value DESC";
}, 2, 10);

A more generic solution for alphanumeric values could be:

add_filter('posts_orderby', function ($orderby, $query) {
    global $wpdb;

    // return early...

    return "{$wpdb->postmeta}.meta_value IS NULL, {$wpdb->postmeta}.meta_value ASC";
}, 2, 10);

IS NULL returns 1 for null values and 0 for not null. In ascending order, 0 (not nulls) will come first. Then we order by the value itself in ascending order too.

Last thing, when using the posts_orderby filter, you don't need these 2 lines anymore:

$query->set('orderby', 'meta_value_num');
$query->set('order', 'ASC');

Edit: the original solution didn't take in consideration non existing meta values. Remove meta_key, meta_query, orderby and order query vars from pre_get_posts and filter the query as follows.

add_filter('posts_clauses', function ($clauses, $query) {
    global $wpdb;

    if (!$query->is_main_query() || !$query->is_home()) {
        return $clauses;
    }

    $meta_key = 'orden_en_categ';

    $clauses['join'] .= " LEFT JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id AND {$wpdb->postmeta}.meta_key = '$meta_key')";
    $clauses['where'] .= " AND ({$wpdb->postmeta}.meta_key = '$meta_key' OR {$wpdb->postmeta}.post_id IS NULL)";
    $clauses['groupby'] = "{$wpdb->posts}.ID";
    $clauses['orderby'] = "-{$wpdb->postmeta}.meta_value+0 DESC";

    return $clauses;
}, 2, 10);

Almost a full custom query but can't figure out a better approach. Hope it helps.

The resulting query will be something like:

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta
   ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'orden_en_categ')
WHERE 1=1
   AND wp_posts.post_type = 'post'
   AND (wp_posts.post_status = 'publish')
   AND (wp_postmeta.meta_key = 'orden_en_categ' OR wp_postmeta.post_id IS NULL)
GROUP BY wp_posts.ID
ORDER BY -wp_postmeta.meta_value+0 DESC
LIMIT 0, 10