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

Question

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

Related Posts: