meta_query sorting by 2 keys

I need to sort (custom) posts by 2 custom field values…

custom field name 1: is_sponsored [ value can either be 1 or 0 ]

custom field name 2: sfp_date [ timestamp aka current post date in seconds ]

Posts whose “is_sponsored” value is 1 need to be on top, sorted by “sfp_date” in DESCending order.
All other posts whose “is_sponsored” value is 0 should be listed below – in descending order (by “sfp_date“) as well.

I have something like:

$sfp_query_args = array(
    'tax_query'   => array( 
        array( 
            'taxonomy' => 'sfp_posts',
            'terms'    => array( 1, 5, 8 )
        )
    ),
    'post_type'   => 'sfpposts',
    'post_status' => 'publish',
    'showposts'   => 15,
    'paged'       => $paged,
    'meta_key'    => 'sfp_date', 
    'orderby'     => 'meta_value_num', 
    'order'       => 'DESC', 
    'meta_query'  => array(
        'key'          => 'is_sponsored',
        'value'        => 2,
        'type'         => 'NUMERIC',
        'compare'      => '<='
    )
);
$wp_q = new WP_Query( $sfp_query_args );

…but not working. Any ideas?

Editors Note: This is a small plugin that should show how the query looks, as we likely don’t have any data set available to test this.

<?php
/** Plugin Name: (#67600) Dump Query parts */
function wpse67600_dump_query_parts( $pieces )
{
    echo '<pre>'.var_export( $pieces, true ).'</pre>';
    return $pieces;
}
add_filter( 'posts_clauses', 'wpse67600_dump_query_parts' );

OP PLEASE ADD OUTPUT OF PLUGIN HERE – use the “edit” link.

EDIT by Dameer

OK, after tracing request and numerous workarounds, I’ve come up with the following…

If I simplify “$sfp_query_args” a little bit the result is close to what’s required, however, inability to sort posts remains as is. Here it is:

$sfp_query_args1 = array(
    'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $cat_id_arr ) ),
    'post_type' => 'sfpposts',
    'post_status' => 'publish',
    'showposts' => (int)$per_page,
    'paged' => $paged,
    'meta_key' => 'is_sponsored', 
    'orderby' => 'meta_value date'
);

• *orderby takes two attributes: meta_value and date*

So $wpdb->request with above arguments in query looks like this:

SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.ID 
FROM $wpdb->posts 
INNER JOIN $wpdb->term_relationships 
ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
INNER JOIN $wpdb->postmeta 
ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) 
WHERE 1=1 
AND $wpdb->posts.post_type="sfpposts" 
AND ($wpdb->posts.post_status="publish") 
AND ($wpdb->postmeta.meta_key = 'is_sponsored' ) 
GROUP BY $wpdb->posts.ID 
ORDER BY $wpdb->postmeta.meta_value, $wpdb->posts.post_date DESC 
LIMIT 0, $per_page

And finally, in order to be able to sort by meta_value as well, query should be set with only one minor difference:

SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.ID 
FROM $wpdb->posts 
INNER JOIN $wpdb->term_relationships 
ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
INNER JOIN $wpdb->postmeta 
ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) 
WHERE 1=1 
AND $wpdb->posts.post_type="sfpposts" 
AND ($wpdb->posts.post_status="publish") 
AND ($wpdb->postmeta.meta_key = 'is_sponsored' ) 
GROUP BY $wpdb->posts.ID 
ORDER BY $wpdb->postmeta.meta_value [!ORDER MISSING!], $wpdb->posts.post_date DESC 
LIMIT 0, $per_page

Please spot [!ORDER MISSING!] placeholder. I guess the above should explain where exactly the problem occurs.

OK, the final workaround would be to split query:

$sfp_query_args = array(
    'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $cat_id_arr ) ),
    'meta_key' => 'is_sponsored',
    'post_type' => 'sfpposts',
    'post_status' => 'publish',
    'showposts' => (int)$per_page,
    'paged' => $paged
);

…and use “posts_orderby” filter to modify ORDER part:

add_filter( 'posts_orderby', 'sfp_modify_orderby' );
function sfp_modify_orderby( $orderby ) {
    if( !is_admin() && is_tax( 'sfp_post_category' ) ) {
        global $wpdb;
        $orderby = " $wpdb->postmeta.meta_value DESC, $wpdb->posts.post_date DESC ";
    }
    return $orderby;
}

The most probably you’ll need to remove filter after the loop on page in order to prevent ‘posts_orderby’ affect any other query (sidebar or footer). So here’s another function to put in “functions.php”:

function sfp_remove_orderby_filter() {
    remove_filter( 'posts_orderby', 'sfp_modify_orderby' );
}

…and on the page using our query discard filter:

if( have_posts() ) : while( have_posts() ) : the_post();
    // code
endwhile;
else :
    // code
endif;

sfp_remove_orderby_filter();

Hopefully it makes sense!