Make WordPress Core

Opened 5 years ago

Closed 2 years ago

Last modified 19 months ago

#15031 closed enhancement (duplicate)

order via meta_query

Reported by: aaroncampbell Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.1
Component: Query Keywords: dev-feedback 2nd-opinion
Focuses: Cc:

Description (last modified by scribu)

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

Change History (23)

comment:1 @aaroncampbell5 years ago

  • Component changed from General to Query

comment:2 @aaroncampbell5 years ago

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.

comment:3 @aaroncampbell5 years ago

  • Keywords dev-feedback added

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.

comment:4 @scribu5 years ago

  • Milestone changed from 3.1 to Future Release
  • Type changed from defect (bug) to enhancement

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.

comment:5 @scribu5 years ago

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

comment:6 @scribu5 years ago

  • Description modified (diff)
  • Summary changed from WP_Query can no longer sort by meta value to order via meta_query

comment:7 follow-up: @scribu5 years ago

I was thinking of something like this:

'meta_query' => array(
    'meta_key' => 'foo',
    'order' => 'ASC',
    'order_priority' => 2
    '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

comment:8 in reply to: ↑ 7 @aaroncampbell5 years ago

  • Keywords 2nd-opinion added

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(
        'meta_key' => 'bar',
        'order' => 'DESC',
        '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.

comment:9 @scribu5 years ago

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

comment:10 @pento5 years ago

  • Cc gary@… added

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

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

comment:11 @scribu5 years ago

Related: #17065

comment:12 @scribu5 years ago

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.

Last edited 5 years ago by scribu (previous) (diff)

comment:13 @pento5 years ago

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

comment:14 @scribu4 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

Closing. See #17065

comment:15 @maorb4 years ago

  • Cc maor@… added

comment:16 @scribu3 years ago

Marked #21617 as dup.

comment:17 @scribu3 years ago

  • Milestone set to Future Release
  • Resolution wontfix deleted
  • Status changed from closed to reopened

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.

comment:18 @husobj3 years ago

  • Cc ben@… added

comment:19 @knutsp3 years ago

  • Cc knut@… added

comment:20 @scribu3 years ago

  • Owner scribu deleted
  • Status changed from reopened to assigned

comment:21 @vanjwilson2 years ago

  • Cc vanjwilson added

comment:22 @wonderboymusic2 years ago

  • Milestone Future Release deleted
  • Resolution set to duplicate
  • Status changed from assigned to closed

There's a Skelton patch on #17065 now - let's close this out, as @scribu hinted at

comment:23 @ircbot19 months ago

This ticket was mentioned in IRC in #wordpress-dev by SergeyBiryukov. View the logs.

Note: See TracTickets for help on using tickets.