WP_Query orderby custom field then post_date in one query

Question

I need to order the first three posts by a custom field, and subsequent posts by post_date in one query - for example:

• Post 4 (field_order: 1)
• Post 3 (field_order: 2)
• Post 2 (field_order: 3)
• Post 1 (field_order: null)
• Post 0 (field_order: null)

Post 1 and Post 0 need to be ordered by post_date.

I can order by custom field easily enough, but when I add a post without the custom field (Post 1 & 0), it doesn't appear in the query.

Below is the query I am currently trying:

    $args = array(
        'post_type' => 'insights',
        'posts_per_page' => '9',
        'meta_query' => array(
            array(
                'key' => 'field_order',
                'value'   => '0',
                'compare' => '>'
            )
        ),
        'orderby' => array(
            'field_order' => 'ASC',
            'post_date' => 'ASC',
        ),
        'order' => 'ASC'
    );

Answer 1

Multiple meta_query arguments with OR

You may use the 'relation' => 'OR' argument in meta_query with two sets of field_order arguments: one with meta_value >= '0' and the other one with NOT EXISTS to generate the main query.

        'meta_query'     => array(
            'relation'   => 'OR',
            field_order' => array(
                'key'       => 'field_order',
                'value'     => '0',
                'compare'   => '>=',
                'type'      => 'NUMERIC'
            ),  
            'field_order_withnulls' => array(
                'key'       => 'field_order',
                'compare'   => 'NOT EXISTS',
                'type'      => 'NUMERIC'
            )
        )

Proper Order by

We can use 'field_order_withnulls' => 'ASC', however, because of the LEFT JOIN, there will be rows with null field_order values & those will come before numeric field_order values in ASC orderby.

To fix that, we will use the ORDER BY -field_order DESC trick as described here.

This will correct the ordering by inverting DESC to ASC, but keeping the rows with null values after the numeric values.

Implementing the - (inverse) operator in orderby

The problem is, WordPress doesn't provide any direct way of setting the - (inverse) operator in orderby. So we will introduce a custom WP_Query attribute named _inverse_order and then use the posts_orderby filter to implement it.

Sample CODE:

    // posts_orderby filter callback function
    // place this function in theme's functions.php file or in a custom plugin
    function wpse311227_inverse_orderby( $orderby, $query ) {
        remove_filter( 'posts_orderby', 'wpse311227_inverse_orderby', 10, 2 );
        $idx = (int) $query->get( '_inverse_order' ) - 1;
        if( $idx >= 0 ) {
            $orders = preg_split( "/(?<=ASC|DESC),[\s]*/i", $orderby );
            if( $idx < count( $orders ) ) {
                $orders[$idx] = '-' . $orders[$idx];
            }
            return implode( $orders, ', ' );
        }

        return $orderby;
    }


    // adding the posts_orderby filter to implement the custom '_inverse_order' attribute
    // this should be placed just before the WP_Query call
    add_filter( 'posts_orderby', 'wpse311227_inverse_orderby', 10, 2 );
    $args = array(
        'post_type'      => 'insights',
        'posts_per_page' => '9',
        'meta_query'     => array(
            'relation'   => 'OR',
            field_order' => array(
                'key'       => 'field_order',
                'value'     => '0',
                'compare'   => '>=',
                'type'      => 'NUMERIC'
            ),  
            'field_order_withnulls' => array(
                'key'       => 'field_order',
                'compare'   => 'NOT EXISTS',
                'type'      => 'NUMERIC'
            )
        ),
        'orderby' => array(
            'field_order_withnulls' => 'DESC',
            'post_date'             => 'ASC'
        ),
        // this custom attribute is implemented in wpse311227_inverse_orderby() function
        // to correct the ordering by placing a '-' operator
        // value of _inverse_order attribute is the position of the
        // orderby attribute to be be inversed,
        // (position starts with 1)
        // in this case, since: 'field_order_withnulls' => 'DESC'
        // is in position 1 of 'orderby' attribute array, so:
        '_inverse_order'  => 1
    );
    $query = new WP_Query( $args );

This will produce all posts with field_order > 0 and the posts that don't have field_order meta data with the expected order.

Note: You'll need to pass a non-empty value in meta_query for a NOT EXISTS check if the WordPress version is below 3.9. Check this note from codex:

Due to bug #23268, value is required for NOT EXISTS comparisons to work correctly prior to 3.9. You must supply some string for the value parameter. An empty string or NULL will NOT work. However, any other string will do the trick and will NOT show up in your SQL when using NOT EXISTS.

---

Warning: This WP_Query will use two LEFT JOIN, which is not very efficient. Although, for even a few thousand posts, this is tolerable. I've tested with 15,000+ posts & the query takes approximately 0.3 second on a average. However, if you have Millions or even hundreds of thousands of posts, then you'll have to optimize the query or find a more efficient method to achieve the same result.

Answer 2

I'm not sure if it can be done on WP_Query, but this should work:

global $wpdb;
$sql = "SELECT p.* FROM {$wpdb->posts} p "
    . " LEFT JOIN {$wpdb->postmeta} pm ON p.id=pm.post_id AND pm.meta_key='field_order' "
    . " WHERE p.post_type='insights' AND p.post_status='publish' "
        . " AND (pm.meta_value > 0 OR pm.meta_value IS NULL) "
    . " ORDER BY -pm.meta_value DESC, p.post_date ASC LIMIT 9";
$posts = $wpdb->get_results($sql);