Multiple options for 'orderby' => 'meta_value_num' or making it optional

[wzislam]

The issue is regarding the WP_Query() and 'orderby' => 'meta_value_num' :

$args = array(
          'cat' => 5,
          'posts_per_page' => 8,
          'meta_key' => 'custom_sort_order',
          'order' => 'ASC',
          'orderby' => 'meta_value_num'
        );

$custom_query = new WP_Query( $args );

With the code above I can sort my posts with my custom field's value order, ascending. But if I forgot to assign the Custom Field key and value with the post, the query doesn't find the whole post because of the custom_sort_order meta_key — because it doesn't exist with that certain post.

But I need to put the 'meta_key' => 'custom_sort_order' there because the Codex says:

'meta_value_num' - Order by numeric meta value (available with Version 2.8). Also note that a 'meta_key=keyname' must also be present in the query.

At present there's no way to define multiple sort order criteria.

Can we have that in any of the upcoming version?

I'm afraid, this is my first trac ticket. We've discussed the issue in ​this WordPress StackExchange thread.

[boonebgorges]

WP does support multiple orderby and order params, since WP 4.0. See ​https://make.wordpress.org/core/2014/08/29/a-more-powerful-order-by-in-wordpress-4-0/.

But your case is different. The root problem has to do with ordering by *meta*, and the way that orderby=meta_value and orderby=meta_value_num work. These params generate SQL that looks like this: SELECT ... FROM wptests_posts INNER JOIN wptests_postmeta ON wptests_posts.ID = wptests_postmeta.post_id WHERE ... wptests_postmeta.meta_key = 'foo'. In other words: the JOIN that enables the ORDER BY clause also excludes posts that don't have meta_key=foo at all. Multiple sort order criteria for meta, while cool, would not solve this.

So, the first enhancement that might come out of this ticket is: the ability to sort by meta_value without requiring that all the items returned have the meta_value. In theory, it's possible to do most of this with a LEFT JOIN. Take the following data:

Posts:

---------   ------------
| Posts |   | Postmeta |
---------   ------------
   ID         post_id  meta_key  meta_value
   --         -------  --------  ----------
   1            2        foo       55
   2            3        foo       75
   3
   4

You can run the following query to get a list of posts that is ordered by meta_value without requiring it:

SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key) ORDER BY wp_postmeta.meta_value DESC, wp_posts.ID ASC;

This does return all posts, and 2 and 3 will be correctly ordered by meta_value. But the IDs that do not join against any rows in the postmeta table will always show up at the top of the list:

1
4
3
2

As far as I can see, there's no way to get around this in a single SQL query (without doing a further subquery or two to determine an exact order, or creating a temporary table). Though I might be wrong about this. It would probably be possible to write something in PHP that would first run a query against wp_postmeta, and then if necessary, would pad out the results with a query against wp_posts. But this is pretty clunky and will require some fairly broad changes to WP_Query.

As for the issue of multiple sort orders and meta_value: That being said, I would also like to have it too, though it wouldn't help with your specific issue (that you want to return results that don't have any value for your key). I brought this up myself a few weeks ago, and sketched a solution https://core.trac.wordpress.org/ticket/25538#comment:26 I'll paste the relevant bits here for posterity's sake:

In the future, I think a better strategy for handling order + meta_query would involve the following:

As WP_Meta_Query transforms meta_query clauses into SQL, it should store an array of the table aliases it creates, and they should get passed back to WP_Query
When WP_Query builds ORDER BY out of orderby=meta_value, it should do some logic like this:

• meta_value corresponding to which column? For this, look at the meta_key query var
• Then look up the table alias for that meta_key in the array passed from WP_Meta_Query::get_sql()
• Use that table alias when building the ORDER BY clause ("ORDER BY mt2.meta_value")
• Maybe build in support for multiple orderby, for tie-breaking etc ("ORDER BY mt2.meta_value ASC, mt1.meta_value DESC")

It's really this last bullet point that addresses your request, but the first couple bullet points are sorta prerequisites.

I'm going to leave this ticket open in Future Release as a placeholder for some of this latter work that I'd like to take up at some point in the not-too-distant future. If you have any thoughts on the above, I'm all ears :)

[boonebgorges]

Related: #19653.

[boonebgorges]

Related: #31045.

[gagan0123]

This issue seems to be resolved, as I'm able to write WP_Query with multiple orderby fields having multiple meta term orders, for eg, a books CPT with meta keys author and publisher, we can sort first by publisher and then by author meta keys using query:

<?php
$query = new WP_Query( array(
        'meta_query'     => array(
                'relation'       => 'AND',
                'author_query'      => array(
                        'key'            => 'author',
                        'compare'   => 'EXISTS',
                ),
                'publisher_query'  => array(
                        'key'            => 'publisher',
                        'compare'   => 'EXISTS',
                ),
        ),
        'orderby'  => array(
                'publisher_query'   => 'ASC',
                'author_query'       => 'ASC',
        ),
        'post_status'   => 'publish',
        'post_type'      => 'book',
) );

[boonebgorges]

Thanks for resurrecting this, @gagan0123 - you are correct that this issue is resolved by #31045. Marking as a duplicate.