[wp-trac] [WordPress Trac] #42031: Orderby with multiple meta keys not ordering correctly

[WordPress Trac]

#42031: Orderby with multiple meta keys not ordering correctly
--------------------------+----------------------
 Reporter:  gekomees      |       Owner:
     Type:  defect (bug)  |      Status:  closed
 Priority:  normal        |   Milestone:
Component:  Query         |     Version:  4.8.2
 Severity:  normal        |  Resolution:  invalid
 Keywords:                |     Focuses:
--------------------------+----------------------
Changes (by boonebgorges):

 * status:  new => closed
 * resolution:   => invalid
 * milestone:  Awaiting Review =>


Comment:

 Hi @gekomees - Thanks for the ticket, and welcome to WordPress Trac!

 The behavior you've described is pretty much by design. When you provide
 multiple values for 'orderby', they're translated to the following MySQL
 syntax: `ORDER BY foo DESC, bar DESC`. This means that items are ordered
 by `foo`, but in cases where `foo` is the same between two rows, `bar` is
 used as a secondary sort - a sort of tiebreaker. See the results at
 https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html for an
 illustration.

 I'm unaware of a MySQL syntax that'll do what you're asking. As such, I
 don't think there's anything WordPress can do to make it possible. As a
 workaround, you might think about storing your data differently (or
 mirroring it) so that the sold/constructing/sale/ready data is stored in a
 single, sortable place - say, a "status" meta key.

 (A side note that, while I know it doesn't matter in your particular case,
 ordering by serialized data is generally kinda nuts.)

--
Ticket URL: <https://core.trac.wordpress.org/ticket/42031#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform