Custom query looking at multiple custom fields and properly sorting

Question

For the past two weeks I've been looking for a way to create a query in wordpress that returns all custom post types where a meta_key "mytype-expired" is NOT set to "yes" and then order the results by a second meta_key called "mytype-sort-order" with the highest number value coming first. If no value exists in the "mytype-sort-order" meta_key field, I want these posts to be displayed order by date (most recently added first), and all of these should be below posts that do have a value.

For example, these set of test posts:

Post 1 (from July 1st, mytype-sort-order value not set), Post 2 (from July 2nd, mytype-sort-order value not set), Post 3 (from July 3rd, mytype-sort-order value not set), Post 4 (from July 4th, mytype-sort-order value = 95), Post 5 (from July 5th, mytype-sort-order value = 90), Post 6 (from July 6th, mytype-sort-order value not set), Post 7 (from July 7th, expired="yes").

should be returned as follows:

1. Post 4 (from July 4th, mytype-sort-order value = 95)
2. Post 5 (from July 5th, mytype-sort-order value = 90)
3. Post 6 (from July 6th, mytype-sort-order value not set)
4. Post 3 (from July 3rd, mytype-sort-order value not set)
5. Post 2 (from July 2nd, mytype-sort-order value not set)
6. Post 1 (from July 1st, mytype-sort-order value not set)

(Post 7 not displayed because it's expired)

I explained my current approach of trying to use meta-query here here, but I found anything new or made any progress myself, and with no responses to my first question I'm wondering if there's another way I could accomplish this. I've been able to not return expired posts, and order by "mytype-sort-order" meta_value_num OR order by date. The problem I keep running into is I can't order by BOTH "mytype-sort-order" meta_value_num AND date Even though I've done a lot with wordpress, my experience with creating custom queries is limited. I'm totally stuck and would REALLY appreciate some ideas. Thanks!

Answer 1

Though it might take some tweaking you can use the 'orderby' parameter on multiple values.

The difficulty here is more in terms of logic, for example July 3 (date value) and the number 10 (meta value) do not really relate to one another, this would cause problems if not formatted correctly.

You best bet is to convert the date to a format that will work with your meta values using the built in WordPress date format, a PHP date format ( most likely a unix timestamp, or timestamp) , or appending a fixed set of values to the date for ordering purposes.

Multiple orderby example. This is not intuitive you have to look closely there are two values separated by a space, "title" AND "menu_order":

$query = new WP_Query( array( 
                     'post_type' => 'page', 
                     'orderby' => 'title menu_order', 
                     'order' => 'ASC' ) );

Date reference:
http://www.php.net/manual/en/datetime.formats.php
http://www.php.net/manual/en/function.strtotime.php
http://codex.wordpress.org/Function_Reference/the_date

Query: http://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

Answer 2

Something like this code snippet will help you

$args = array(
    'post_type' => 'product',
    'orderby' => 'meta_value', 
    'meta_key' => 'mytype-sort-order',
    'order' => 'ASC',
    'meta_query' => array(
        array(
            'key' => 'mytype-expired',
            'value' => 'yes',
            'compare' => '!='
        )
    )
);
$query = new WP_Query( $args );