WP_Query orderby and order options not used as expected

[beaulebens]

In WP_Query, "orderby" allows you to specify multiple columns, separated by spaces, to order your results by.

You can only specify a single "order" value (ASC or DESC).

If you specify multiple values in "orderby", "order" is applied to the end of the list, or effectively to the last "orderby" column.

e.g. If a custom query specifies:

'orderby' => 'title date', 'order' => 'DESC'

it probably meant ORDER BY post_title DESC, post_date DESC (or maybe ASC, it's ambiguous), but I'll actually get:

ORDER BY post_title, post_date DESC

With MySQL's implied ASC on ORDER BY statements, this is the opposite of what I really wanted.

We should either drop support for multiple-column ordering, allow for a way to specify a sort order per orderby/column, or at the very least apply the "order" to the first column specified, which seems like it'd be more likely to be intended outcome.

See Also: #9978 for a sticky post bug that will need to implement this ordering logic as well.

[Denis-de-Bernardy]

Underneath this is a revamp of the query handling. Suggesting wontfix, personally.

[Denis-de-Bernardy]

we could also proceed as follows:

split orderby clause on space, split order clause on space, merge the two back into the query.

[interconnectit]

Patch for query.php to add multiple orders when using many orderby statments

[interconnectit]

The diff I've just added lets you add more than one 'order', space separated, to queries that have more than one 'orderby'. If you add three 'orderby' statements and 2 'order' then the 3rd 'orderby' will have the first 'order'. If you were to add a fourth 'orderby' and leave the 'order' at 2 then the 4th 'orderby' would have the 2nd 'order'.

eg.

get_posts( array( 'orderby' => 'menu_order title', 'order' => 'DESC ASC' );
Would sort by descending menu_order and ascending alphabetic title order.

get_posts( array( 'orderby' => 'menu_order title date', 'order' => 'DESC ASC' );
Would sort by descending menu_order and ascending alphabetic title order and descending date. Same as adding 'order' => 'DESC ASC DESC'

[scribu]

Separating 'order' from 'orderby' is not a good idea. You quickly loose track of which is which.

Closing as duplicate of #17065 which has a more complete patch.