order via meta_query

[aaroncampbell]

#14645 added the ability to query based on multiple meta keys, but you can't use it to order posts.

The plan is to add another array element to each meta_query array called 'order'. You would be able to pass ASC or DESC and empty or not set would mean "don't order based on this". I'm also incorporating the +0 fix into this if you pass 'numeric'=>true so that the order is as expected for numeric types.

Adding the capability to _wp_meta_sql() was pretty simple. However, where it's currently called from any order by options would be appended to whatever was already calculated, which means that meta data ordering would always have to be secondary. You could NOT order by 'some_meta_value, date' only by 'date,some_meta_value' or just 'some_meta_value'. If we move it to being processed BEFORE the existing order_by is processed, then the reverse is true.

It seems like the correct solution would actually be to have some kind of place holder to be allowed, and roll back to appending if no place holders were used?

Sounds like a pain, so I'd like some input.

It turns out this combination still works:

query_posts( array(
  'meta_key' => 'foo',
  'orderby' => 'meta_value'
) );

So it's not a regression.

Moving to Future until we can figure out a better solution.

(In [15721]) Put the simple meta query first, so that orderby=meta_value works even when using 'meta_query'. See #15031

I was thinking of something like this:

'meta_query' => array(
  array(
    'meta_key' => 'foo',
    'order' => 'ASC',
    'order_priority' => 2
  ),
  array(
    'meta_key' => 'bar',
    'order' => 'DESC',
    'order_priority' => 1
  ),
)

which would result in this SQL:

ORDER BY wp_postmeta_alias2.meta_value DESC, wp_postmeta_alias1.meta_value ASC

Replying to scribu:

I like the idea, but I have a couple questions:

1. Do we need order_priority? Can we just ask that people add the meta queries in the order they want them handled? To create the same sql as you have above you'd just do this:

   'meta_query' => array(
     array(
       'meta_key' => 'bar',
       'order' => 'DESC',
     ),
     array(
       'meta_key' => 'foo',
       'order' => 'ASC',
     ),
   )
   

2. Also, How do we plan to integrate these order clauses with the existing order stuff? For example, would it be possible to sort by "date, meta_value" or "meta_value, date"? Maybe the order_priority could be useful here, assuming that other order clauses are priority x, and anything with a priority < x comes before and anything with priority > x comes after? I know we don't have to offer every single option, but I think we need to decide what would be useful and make sure we account for that.

I'd also like to bounce this off a couple more people and get additional feedback.

1. I guess implicit priority could work; the traditional 'order' => 'meta_value' would always be first.

2. No clue.

For 2, how about allowing orderby to take a string, or an array? It maintains backwards compatibility, but would also allow:

'orderby' => array(
  array(
    'meta_key' => 'bar',
    'order' => 'DESC',
  ),
  array(
    'key' => 'date',
    'order' => 'DESC',
  ),
  array(
    'meta_key' => 'foo',
    'order' => 'ASC',
  ),
)

Related: #17065

I guess that in the end, allowing an array of values to 'orderby' makes more sense than an 'order' parameter in 'meta_query':

MySQL allows you to order items by a value, even if it's null. The fact that we required meta_key to be set, in order to set 'orderby' => 'meta_value' is just a limitation on our system.

So, I'm thinking of closing this as wontfix and focus on #17065.

If #17065 will cover meta keys as well, that sounds reasonable to me.

Closing. See #17065

Marked #21617 as dup.

Going to re-open this, as 'wontfix' isn't the appropriate resolution.

If #17065 is implemented and covers this, we can close it as a dup.