Query by one meta_key and sort by another (possibly NULL value)

Question

I want to query by 1 meta key, but sort by another key that may or may not exist, and then by post date

So the end result would be something like:

Meta Key A / Date 1
Meta Key A / Date 2
Meta Key B / Date 1
No Meta Key / Date 1
No Meta Key / Date 2

Query args like the following are not returning any posts:

$customer_orders = get_posts( array(
    'numberposts' => $order_count,
    'meta_query' => array(
        array(
            'key'     => '_customer_user',
            'value'   => get_current_user_id(),
            'compare' => '=',
        ),
        array(
            'key'     => '_dealer_number',
            'compare' => 'EXISTS',
        ),
        array(
            'key'     => '_dealer_number',
            'compare' => 'NOT EXISTS',
        ),
        'relation' => 'AND'
    ),
    'orderby'    => 'meta_value date',
    'order'      => 'ASC'
) );

Is this possible with WP_Query args or do I need to filter posts_orderby? If so, what might I use?

Answer 1

I don't think there's a way to do it without using filters. Using posts_clauses you could do:

function wpse163696_posts_clauses( $pieces, $query ) {
    if ( $query->get( 'orderby' ) != 'dealer_date' ) {
        return $pieces;
    }
    global $wpdb;

    $order = $query->get( 'order' );
    $pieces[ 'join' ] .= $wpdb->prepare(
        ' LEFT JOIN ' . $wpdb->postmeta . ' dealer_pm ON dealer_pm.post_id = ' . $wpdb->posts . '.ID AND dealer_pm.meta_key = %s'
        , '_dealer_number' );
    $pieces[ 'orderby' ] = 'ISNULL(MAX(dealer_pm.meta_value)) ' . $order . ', MAX(dealer_pm.meta_value) ' . $order  . ', ' . $wpdb->posts . '.post_date ' . $order;
    return $pieces;
}

Then your query becomes:

add_filter( 'posts_clauses', 'wpse163696_posts_clauses', 10, 2 );
$customer_orders = get_posts( array(
    'numberposts' => $order_count,
    'meta_query' => array(
        array(
            'key'     => '_customer_user',
            'value'   => get_current_user_id(),
            'compare' => '=',
        ),
    ),
    'orderby'    => 'dealer_date',
    'order'      => 'ASC',
    'suppress_filters' => false,
) );
remove_filter( 'posts_clauses', 'wpse163696_posts_clauses', 10 );

Answer 2

You have a couple of errors there, I believe.

1. There is no such parameter as 'numberposts'. Perhaps you mean 'posts_per_page'? That is what you should use to determine the number of posts to display.

2. When ordering by meta value, the correct syntax should be:

'orderby' => 'meta_value',
'meta_key' => 'key_name', 

Where key_name is the custom field whose meta_value you are using for the orderby.

So based on your question, your query may look like this:

$customer_orders = get_posts(
  array(
    'posts_per_page' => $order_count,
    'no_found_rows' => true,
    'meta_query' => array(
      array(
        'key'     => '_customer_user',
        'value'   => get_current_user_id(),
        'compare' => '=',
      ),
      array(
        'key'     => '_dealer_number',
        'compare' => 'EXISTS',
      ),
      array(
        'key'     => '_dealer_number',
        'compare' => 'NOT EXISTS',
      ),
      'relation' => 'AND'
    ),
    'orderby'    => 'meta_value',
    'meta_key'   => 'date',
    'order'      => 'ASC'
  )
);

(Note that I added 'no_found_rows'. If you know how many posts you are going to query and do not need pagination, then this addition will greatly speed up your query).