Independent ASC/DESC in multiple ORDER BY statement.

[ericmann]

WP_Query supports ordering results by multiple columns, but does not currently support independent ASC/DESC declarations on those columns. Instead, it concatenates the ORDER and ORDER BY statements together.

In some cases, it would be beneficial to independently set the order for these columns. An example offered on WP-Hackers:

I have a situation where I want to order a list of post both by author and date using WP_Query which isn't a problem since 'orderby' lets me do that. But I want the author part sorted ascending and date part sorted descending.

A normal MySql statement would end with ORDER BY author ASC, date DESC.

We should extend WP_Query to allow users to set their ordering parameters independently. This should be done in such a way as to not break backwards compatibility.

Any syntax proposals?

Not at the moment. My first attempt allowed users to pass an array into 'orderby' rather than just a string. So:

$args = array(
    'post_type' => 'post'
    'orderby' => array(
        'author ASC',
        'date DESC'
    )
);

The advantage to this approach is that it wouldn't break backwards compat. Disadvantage is that is combines the behavior of 'orderby' and 'order' and could confuse people ... particularly if they use both parameters.

That's a good start.

While thinking about this, we also have to consider meta queries: #15031

c/p my syntax proposal from #15031. Allows for backwards compatibility, doesn't combine 'orderby' and 'order', allows for meta queries:

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

The disadvantage is multi-level arrays, but we're already using them for 'tax_query'.

I would replace 'key' with 'field', to avoid confusion with 'meta_key'.

An alternate syntax could be:

1. $args = array(
  'post_type => 'post',
  'orderby' => array(
    array(
      'meta_key' => 'bar',
      'order' => 'DESC',
    ),
    'date DESC'
  )
)

ie. Only use a multi dimension array when it's actually needed, Since we've currently got orderby as well as order, we could simplify it to (ie. abide by $order if set)

1b. $args = array(
  'post_type => 'post',
  'order' => 'DESC',
  'orderby' => array(
    array( 'meta_key' => 'bar' ),
    'date'
  )
)

The other obvious one, which I don't really like due to the "new" syntax of it:

2. $args = array(
  'post_type => 'post',
  'orderby' => array(
    'meta_key:bar DESC',
    'date DESC'
  )
)

And to drop a bombshell here, One thing that I've had to implement "often" when someone wants a custom ordering, is to float one value to the front of the list, such as:

3. $args = array(
  'post_type => 'post',
  'orderby' => array(
    array(
      'meta_key' => 'bar',
      'meta_value' => 'first',
      'order' => 'DESC', // Ie. "ORDER BY (meta_key_bar === 'first') DESC"
    ),
    'date DESC'
  )
)

This one is a step over what we currently have however, and can easily be achieved through existing filters, so whatever is added here, we'll need to ensure that extending upon it is possible.

I'll whip up a patch for number 1 above and a example of implementing 3 in a plugin..

I don't really like 'date DESC'. I'm ok with 'date' instead of array( 'field' => 'date' ) though.

And using the 'order' query var as a default makes sense too.

I don't really like 'date DESC'

Hm, I thought we already supported that, but appears not, So disregard that syntax. (I have however seen people attempting to use that syntax, thanks to the way WP_Query handles it, it'd just silently ignore the DESC it seems)

attachment 17065.diff added

• Implements multiple orderby's
• Orders by any Queried Meta Key Value
• Retains full back compat
• Implemented 3. from above, See the final binary example below, for interests sake, thats lines 2371 & 2372 in the patch - To implement that in a plugin would require to filter each orderby clause individually, or to duplicate most of the block, or to perform black magic (regular expressions)
• If no valid ordering is specified, it falls back to post_date as before

Usage:
(current usage, which still works)
orderby: 'date'
orderby: 'date' order: ASC
orderby: 'date title' order: DESC (Orders by date DESC, and title DESC)
orderby: 'MyMetaKey'

(New functionality)
order: ASC // If the order field is not specified by a item, this is used.
orderby: array( 'date' )
orderby: array( 'date', 'title' )
orderby: array(
            array(
               'field' => 'date',
               'order' => 'DESC'
            )
          );
orderby: array(
            array(
               'field' => 'date',
               'order' => 'DESC'
            ),
            array(
               'field' => 'title',
            )
          );
orderby: array(
            array(
               'meta_key' => 'MyMetaKey', // meta_key || meta_query MUST query for this key, else it's ignored
               'order' => 'DESC'
            ),
            array(
               'field' => 'title',
               'order' => 'ASC'
            )
          );
orderby: array(
            array(
               'meta_key' => 'MyMetaKey', // defaults to ordering by meta_value
            ),
            array(
               'meta_key' => 'MyMetaKey', // meta_key || meta_query MUST query for this key, else it's ignored
               'field' => 'meta_value_num', // Lets numerically order it
               'order' => 'DESC'
            )
          );

Binary Ordering:
orderby: array(
            array(
               'field' => 'ID',
               'value' => 10, // Binary ordering, This moves ID 10 to the front, or back of the set. Can be used with any field.
               'order' => 'DESC'
            )
          );
orderby: array(
            array(
               'field' => 'ID',
               'value' => 10, 
               'order' => 'DESC'
            ),
            'rand' // post ID 10 is at the start of the list, Remaining items are randomly sorted.
          );

Marked #9979 as duplicate.

My first trac comment - inspired by WordCamp GC. :)

Possible stupid question - why do we require the orderby meta_key to be part of the meta_query?

What about the usage case where I want to return results including but not limited to items that include the meta_key, and order by that meta_key. For example, I want to order posts by custom_meta_item, but I want to include posts that don't have any custom_meta_item.

This could be achieved by adding a second postmeta INNER JOIN to $pieces [ 'join' ], and using CASE to order in $pieces [ 'order' ] .

We could use the same syntax, but avoid ignoring orderby [ 0 ] [ 'meta_key' ] if it's not included in the meta_query.

Thoughts?

why do we require the orderby meta_key to be part of the meta_query?

Quite simply, because otherwise, it's not included in the JOIN's which are used. We could add an extra join for the meta_key that it's being ordered by, but with the move of the meta queries into a seperate function, that might be more difficult and dirty than it sounds.

inspired by WordCamp GC. :)

I'm glad it got at least one comment out of it, stick around and make another dozen yeah? :)

rosariobocca, trac is for discussing code changes to WordPress Core.

For questions about how to do a particular thing using WordPress, please use the support forums:

​http://wordpress.org/support/

If you tried that and got no reply, try posting to the wp-hackers mailing list.

In either case, here is not the best place to ask.

sorry.

Replying to scribu:

rosariobocca, trac is for discussing code changes to WordPress Core.

For questions about how to do a particular thing using WordPress, please use the support forums:

​http://wordpress.org/support/

If you tried that and got no reply, try posting to the wp-hackers mailing list.

In either case, here is not the best place to ask.

Really digging this new syntax. Great stuff.

dd32,

I'm using 3.3.1, and I really need to be able to sort by more than 1 custom field, has this been implemented? I can't find about this in the codex. Can I still implement your modifications? query.php has obvisouly changed over the past 11 months.

Thanks!

-Dave

Love to see this integrated as well. Apparently nobody needs this bad enough :-)

The new syntax looks great. Looking forward to seeing this happen as well.

Might be nice to break this out into a new class, similar to WP_Meta_Query and WP_Tax_Query, especially for testing purposes.

For this to end up in core, we'll need two sets of tests: One, some unit tests that assert the resulting SQL based on various examples. Two, test how it works when integrated into WP_Query in terms of properly ordering posts. The integration tests should be based on a set of posts, similar to tests/query/results.php.

I still disagree with testing the actual SQL string. A single extra or missing whitespace can make the test fail, even though the SQL is actually correct.

Replying to scribu:

...A single extra or missing whitespace can make the test fail...

Was looking at that when the test is on HTML code too (for wpautop). A simple normalize_whitespace regex is enough to fix this (needs to run on both strings before comparing them):

$str = preg_replace( '/[\r\n\t ]+/', ' ', $str );

\s is not good as it "eats" parts of some utf-8 chars.

That was just one example. Here's another:

INNER JOIN wp_posts AS p_to_orderby

vs.

INNER JOIN wp_posts AS posts_orderby

The alias is arbitrary and doesn't affect the results, but it will make the test fail.

Any plans to implement this in 3.6? It's been 2 years ...