Context Navigation

← Previous Ticket
Next Ticket →

#42031 closed defect (bug) (invalid)

Orderby with multiple meta keys not ordering correctly

Reported by:	gekomees	Owned by:
Priority:	normal	Milestone:
Component:	Query	Version:	4.8.2
Severity:	normal	Keywords:
Cc:		Focuses:

Description

I have a meta field in the database as an array. Looks something like this:
a:2:{i:0;s:4:"sold";i:1;s:12:"constructing";}
An array containing any amount of four possible values.

Basically, the meta_query I have is this:

<?php
'meta_query' => array(
        'relation' => 'OR',
        'on_sale' => array(
                'key' => 'acf_project_status',
                'value' => 'on_sale',
                'compare' => 'LIKE'
         ),
        'constructing' => array(
                'key' => 'acf_project_status',
                'value' => 'constructing',
                'compare' => 'LIKE'
         ),
        'ready' => array(
                'key' => 'acf_project_status',
                'value' => 'ready',
                'compare' => 'LIKE'
         ),
        'sold' => array(
                'key' => 'acf_project_status',
                'value' => 'sold',
                'compare' => 'LIKE'
         )
),

And the orderby:

<?php
'orderby' => array(
        'on_sale' => 'DESC',
        'constructing' => 'DESC',
        'ready' => 'DESC',
        'sold' => 'DESC',
)

What I expect to happen is the posts ordered as such:
1) posts, that have on_sale
2) posts, that have constructing
3) posts, that have ready
4) posts, that have sold

Posts that have on_sale indeed are first, but after that no more sorting is applied. The rest of the results come out as is.
For example I had 2 on_sale show up first but a post having "constructing" was dead last after readies and solds.

I don't think the problem is in searching from array with LIKE since by themselves WP_Query can manage all the meta_queries successfully.
I have also tried the following format for orderby:

<?php
'orderby' => 'on_sale constructing ready sold'

Of course, this didn't change anything.

Change History (1)

#1 @boonebgorges
9 years ago

Milestone Awaiting Review
Resolution → invalid
Status new → closed

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.)

Note: See TracTickets for help on using tickets.

Download in other formats:

Make WordPress Core