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.

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:

$pageQuery = new WP_Query([
    'meta_query' => [
        'relation' => 'OR',
        ['key' => 'item_price', 'compare' => 'NOT EXISTS'], // this comes first!
        ['key' => 'item_price', 'compare' => 'EXISTS'],
    ],
    'order' => 'DESC',
    'orderby' => 'meta_value_num',
    'post_status' => 'publish',
    'post_type' => 'page',
    'posts_per_page' => 10,
]);

I also encountered a similar problem and the following solution helped me:

$args = array(
'post_type' => 'kosh_products',
'posts_per_page' => -1,
'meta_query' => array(
    'relation' => 'OR',
    'category_sort_order' => array(
        'key' => '_sort_order',
        'compare' => 'EXISTS'
    ),
    'category_sort_order_not_exists' => array(
        'key' => '_sort_order',
        'compare' => 'NOT EXISTS'
    ), 
),
'orderby' => array( 
    'category_sort_order' => 'ASC',
    'date' => 'ASC'
));
$query = new WP_Query( $args );

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_keys, one that all posts have (like "_thumbnail_id"), and the meta_key you wish use as filter.

So your args:

$qry->set(
    'meta_query',
    array(
        'relation' => 'OR',
        array(
            'key' => 'item_price', 
            'value' => '', 
            'compare' => 'EXISTS'
        ),
        array(
            'key' => 'item_price', 
            'value' => '', 
            'compare' => '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');

If suitable, you can add a default meta value each time a post is saved or updated, if the meta value does no exists.

function addDefaultMetaValue($post_id) {
    add_post_meta($post_id, 'item_price', 0, true);
}
add_action('save_post', 'addDefaultMetaValue');

If you are using a custom post type, replace the add_action('save_post', 'addDefaultMetaValue'); by add_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:

$query->set( 'orderby', 'meta_value_num' );
$query->set( 'meta_query', [
    'relation' => 'OR',
    [ 'key' => 'your_meta_name', 'compare' => 'NOT EXISTS' ],
    [
        'key' => 'your_meta_name',
        'compare' => 'EXISTS',
    ],
] );

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.

<?php
$paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
$args = [
    'post_type' => 'business',
    'posts_per_page' => 12,
    'meta_query' => [
        'relation' => 'OR',
        [
            'key' => 'is_premium', 
            'compare' => 'NOT EXISTS' // NOT EXISTS first is important
        ],
        [
            'key' => 'is_premium', 
            'compare' => 'EXISTS'
        ],
    ]
    ,
    'orderby' => [
        'meta_value' => 'DESC', // This is the is_premium field
        'title' => 'ASC',
    ],
    'paged' => $paged
];

// Add our own join to the query
function custom_join($join) {
    global $wpdb;

    if( ! is_admin() ) {
        $join .= $wpdb->prepare(
        ' LEFT JOIN ' . $wpdb->postmeta . ' cpm ON cpm.post_id = ' . $wpdb->posts . '.ID AND cpm.meta_key = %s'
        , 'is_premium' );
    }

    return $join;
}
add_filter('posts_join','custom_join');

// Add our own order by to the query
function custom_orderby($orderby_statement){
    global $wpdb;

    if ( ! is_admin() ) {
        // Here we order by the meta_value ONLY is it's 1, otherwise ignore it for that order statement, which 
        // means it'll be ordered by the next statement (title), the same as the other results
        $orderby_statement = "IF(cpm.meta_value = 1, 1, 0) DESC, wp_posts.post_title ASC ";
    }

    return $orderby_statement;
}
add_filter('posts_orderby','custom_orderby', 10, 2 ); 

// Query
$business = new WP_Query( $args );

// Remove the filters
remove_filter('posts_orderby','custom_orderby');
remove_filter('posts_join','custom_join');

There’s a possible orderby value of meta_value for that.

$query = new WP_Query( array ( 
    'meta_key'   => 'your_keys_name',
    'orderby'    => 'meta_value',
    'order'      => 'DESC',
    'meta_query' => array( array(
         'key'     => 'your_meta_key',
         'value'   => '',
         'compare' => 'NOT EXISTS',
         // 'type'    => 'CHAR',
    ) )
) );

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 and key 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 != (”):

$query = new WP_Query( array ( 
    'orderby'    => 'meta_value_num',
    'order'      => 'DESC',
    'meta_query' => array( array(
         'key'     => 'item_price',
         'value'   => '',
         'compare' => '!=',
    ) )
) );

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! 🙂

add_filter('posts_join_paged', 'edit_join', 999, 2);
add_filter('posts_orderby', 'edit_orderby', 999, 2);

/**
 * Edit join
 *
 * @param string $join_paged_statement
 * @param WP_Query $wp_query
 * @return string
 */
function edit_join($join_paged_statement, $wp_query)
{
    global $wpdb;
    if (
        !isset($wp_query->query)
        || $wp_query->is_page
        || $wp_query->is_admin
        || (isset($wp_query->query['post_type']) && $wp_query->query['post_type'] != 'my_custom_post_type')
    ) {
        return $join_paged_statement;
    }

    $join_to_add = "
        LEFT JOIN {$wpdb->prefix}postmeta AS my_custom_meta_key
            ON ({$wpdb->prefix}posts.ID = my_custom_meta_key.post_id
                AND my_custom_meta_key.meta_key = 'my_custom_meta_key')
    ";

    // Only add if it's not already in there
    if (strpos($join_paged_statement, $join_to_add) === false) {
        $join_paged_statement = $join_paged_statement . $join_to_add;
    }

    return $join_paged_statement;
}

/** 
 * Edit orderby
 *
 * @param string $orderby_statement
 * @param WP_Query $wp_query
 * @return string
 */
function edit_orderby($orderby_statement, $wp_query)
{
    if (
        !isset($wp_query->query)
        || $wp_query->is_page
        || $wp_query->is_admin
        || (isset($wp_query->query['post_type']) && $wp_query->query['post_type'] != 'my_custom_post_type')
    ) {
        return $orderby_statement;
    }

    $orderby_statement = "my_custom_meta_key.meta_value DESC";

    return $orderby_statement;
}

This solution worked for me:

add_action( 'pre_get_posts', 'orden_portfolio' );
function orden_portfolio( $query ) {

    if( ! is_admin() ) {

        $query->set( 'orderby', 'meta_value_num' );
        $query->set( 'order', 'ASC' );
        $query->set( 'meta_query', [
            'relation' => 'OR',
            [ 
                'key' => 'ce_orden', 
                'compare' => 'NOT EXISTS' ],
            [
                'key' => 'ce_orden',
                'compare' => 'EXISTS',
            ],
        ] );

        return $query;

    }

}

However, this solution first shows the records with null meta_value. This other solution shows ASC order and nulls at the end:

function custom_join($join) {
    global $wpdb;

    if( ! is_admin() ) {
        $join .= $wpdb->prepare(
        ' LEFT JOIN ' . $wpdb->postmeta . ' cpm ON cpm.post_id = ' . $wpdb->posts . '.ID AND cpm.meta_key = %s'
        , 'ce_orden' );
    }

    return $join;
}

add_filter('posts_join','custom_join');

function custom_orderby($orderby_statement){
    global $wpdb;

    if ( ! is_admin() ) {
        $orderby_statement = "CAST( COALESCE(cpm.meta_value,99999) as SIGNED INTEGER) ASC";
    }

    return $orderby_statement;
}

add_filter('posts_orderby','custom_orderby', 10, 2 ); 

I was able to solve this by using one query and proper args. WP 4.1+

$args = array( 
    'posts_per_page' => -1, 
    'post_status' => 'publish',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'custom_sort',
            'compare' => 'EXISTS'
        ),
        array(
            'key' => 'custom_sort',
            'compare' => 'NOT EXISTS'
        )
    ),
    'orderby' => 'meta_value_num title',
    'order' => 'ASC',
));

$query = new WP_Query($args);

See my full answer here: https://wordpress.stackexchange.com/a/370841/15209