How to query_posts using meta_query to orderby meta_key AND have a secondary sort by date?

Question

I've been troubleshooting an issue over the past few days and keep going in circles. Could really use a fresh pair of eyes to help me answer this question...

So I'm working with a wordpress site where posts have a few custom fields relevant to the query I need to run: "post-expired" and "my-sort-order". When I run my query_posts I want the results to be posts where the "post-expired" is NOT "yes" (this part works in my code). I also want the results sorted by the DESC number value of "my-sort-order" (works as well) AND if there's a tie on "my-sort-order" I would like a secondary sort by date with the most recent posts first.

The secondary sort by date is where I'm having trouble.

Here is the code I have right now:

<?php               
$args = array(
    'paged' => $paged,
    'meta_query'=> array(
        array(
            'key'=>'post-expired',
            'value'=> 'yes',
            'compare' => '!='
        )
    ),
    'meta_key'=>'my-sort-order',
    'orderby'=>'meta_value_num', 
    'order'=>'DESC'
);

$args = array_merge( $args , $wp_query->query );

query_posts( $args );
?>

Using the code above I correctly get back the non-expired posts and they're sorted by my-sort-order descending (i.e. 100 shows up before 99, etc). But if both posts have the same sort order value there is some type of secondary sort happening I can't seem to control (and can't figure out what it is actually being sorted on).

My first idea to solve this was to only add values to "my-sort-order" if I wanted to set a specific sort order for that post. I thought if the value for this field was left blank for the rest of the posts, they would simply be returned in the default sort by DESC date (after the posts with a set sort order). However, what actually happened was any posts that didn't have a sort order value set were NOT returned at all...

Next, I tried adding multiple values in the orderby field like this:

('orderby'=>'meta_value_num date')

That completely blew up both sort orders I was going for and returned the posts in an unexpected way. I was under the impression that multiple orderby values were allowed, but for some reason it's not working here.

At this point I'm not sure how to make either of these potential solutions work. Does anyone know how I can either 1) Sort posts by the "my-sort-order" field first, then have the remaining posts with no value in that field returned with the default date sort; or 2) Figure out how to control the secondary orderby sort so any posts that have a tie on "my-sort-order" will be sorted by date (newest first)?

Answer 1

Hopefully you've figured this out by now, but if you haven't you should be able to use the "posts_orderby" filter to dial in a specific order for your query. I'm not going to give a full solution here, but you can refer to this post for more: http://mitcho.com/blog/how-to/external-orders-in-wordpress-queries/

EDIT:

Here's the Documentation - basically you can just override the ORDER BY clause of the SQL: http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_orderby

And here's the example code from the docs:

add_filter('posts_orderby', 'edit_posts_orderby');
add_filter('posts_join_paged','edit_posts_join_paged');

function edit_posts_join_paged($join_paged_statement) {
$join_paged_statement = "LEFT JOIN wp_gdsr_data_article gdsra ON gdsra.post_id = wp_posts.ID";
return $join_paged_statement;   
}

function edit_posts_orderby($orderby_statement) {
$orderby_statement = "(gdsra.user_votes_total_sum/gdsra.user_votes_count) DESC";
return $orderby_statement;
}