Ordering by meta_value AND date NOT WORKING with wp_query

Question

I want to select posts and custom post type with a specific meta value (mb_home!=0), of last week and ORDER them by mb_home. If they have the same mb_home value, I want them to be ordered by date. Both order have to be DESC.

I am using this query but it just does not work:

$query = new WP_Query( array(
            'post_type' => array ('post', 'aggregato'),
            'posts_per_page' => 13,
            'post__not_in' => $linked_posts,
            'meta_key' => 'mb_home',
            'orderby' => 'meta_value_num date',
            'order' => 'DESC',
            'meta_query' => array(
                                array(
                                        'key' => 'mb_home',
                                        'value' => '0',
                                        'compare' => '>',
                                    )
                            ),
            'date_query' => array(
                                    'after' => '1 week ago',
                            )
            ));

If I drop date and use 'order by' => 'meta_value_num' the query gives me back the results correctly ordered by mb_home DESC. But if they have the same mb_home value, they are ordered by date ASC.

If I try to change the way I order stuff, using this syntax:

'orderby' => array( 'meta_value_num' => 'DESC', 'date' => 'DESC' ),

instead of the above orderby and order statements, it just falls back to date DESC ignoring completely the mb_home value.

All these results are the same with meta_value instead of meta_value_num. Nothing changes.

Please, give me some pointers, because I've been over this for almost a month, trying everything but not succeeding.

Oh masters of wordpress! I invoke your help!!

Answer 1

You have to use the posts_orderby filter to do this at the mo, eg

function wpse159469_posts_orderby( $orderby, $query ) {
    return implode( ' DESC,', explode( ',', str_replace( array(' ASC', ' DESC' ), '', $orderby ) ) ) . ' DESC';
}

then around your query:

add_filter( 'posts_orderby', 'wpse159469_posts_orderby', 10, 2 );
$query = new WP_Query( array( // etc
remove_filter( 'posts_orderby', 'wpse159469_posts_orderby', 10 );