WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#36937 closed defect (bug) (invalid)

Order By Multiple Meta Keys doesn't work as expected

Reported by: alexdelgado Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.5.2
Component: Query Keywords: close
Focuses: Cc:
PR Number:

Description

I have a situation in which I want to order posts by two meta keys so I wrote the following code and expected the query to sort using both keys, however, the resulting query only sorted the query by the very first orderby key/pair.

$args =
    array(
        'post_type'              => 'post',
        'post_status'            => 'publish',
        'posts_per_page'         => 1,
        'no_found_rows'          => true,
        'update_post_meta_cache' => false,
        'orderby'                => array(
            'key1'               => 'DESC',
            'key2'               => 'ASC',
        ),
        'meta_query'             => array(
            'relation'           => 'AND',
            array(
                'key'            => 'key1',
                'value'          => 'value1',
                'compare'        => '='
            ),
            array(
                'key'            => 'key2',
                'type'           => 'NUMERIC',
                'compare'        => 'EXISTS',
            )
        )
    );

$query = new WP_Query( $args );

// $query->request:
SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1
    AND ( ( wp_postmeta.meta_key = 'key1' AND CAST(wp_postmeta.meta_value AS CHAR) = 'value1' ) AND  mt1.meta_key = 'key1' )
    AND wp_posts.post_type = 'post'
    AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value ASC
LIMIT 0, 1

I tried changing the order of the 'orderby' array and 'meta_query' array, but each time the query returned a single order by clause and it was always the first 'orderby' key/value pair.

Then, I tried the following, and I got the expected query.

$args =
    array(
        'post_type'              => 'post',
        'post_status'            => 'publish',
        'posts_per_page'         => 1,
        'no_found_rows'          => true,
        'update_post_meta_cache' => false,
        'orderby'                => array(
            'key1'               => 'DESC',
            'mt1'                => 'ASC',
        ),
        'meta_query'             => array(
            'relation'           => 'AND',
            array(
                'key'            => 'key1',
                'value'          => 'value1',
                'compare'        => '='
            ),
            array(
                'key'            => 'key2',
                'type'           => 'NUMERIC',
                'compare'        => 'EXISTS',
            )
        )
    );

$query = new WP_Query( $args );

// $query->request:
SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1
    AND ( ( wp_postmeta.meta_key = 'key1' AND CAST(wp_postmeta.meta_value AS CHAR) = 'value1' ) AND mt1.meta_key = 'key2' )
    AND wp_posts.post_type = 'post'
    AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC, CAST(mt1.meta_value AS SIGNED)
LIMIT 0, 1

For some reason they key lookup only works for the first order by clause. I didn't dig too deep to see if this is just a variable being set during the first run and then not being reset, but I would hazard a guess that it's something like that.

Change History (5)

#1 @alexdelgado
3 years ago

  • Summary changed from Order By Multiple meta keys doesn't work as expected to Order By Multiple Meta Keys doesn't work as expected

#2 @Funkatronic
3 years ago

  • Keywords close added
  • Resolution set to invalid
  • Status changed from new to closed

To order by multiple keys, you have to use different syntax:

<?php
$args =
    array(
        'post_type'              => 'post',
        'post_status'            => 'publish',
        'posts_per_page'         => 1,
        'no_found_rows'          => true,
        'update_post_meta_cache' => false,
        'orderby'                => array(
            'key1'               => 'DESC',
            'key2'               => 'ASC',
        ),
        'meta_query'             => array(
            'relation'           => 'AND',
            'key1' => array(
                'key'            => 'key1',
                'value'          => 'value1',
                'compare'        => '='
            ),
            'key2' => array(
                'key'            => 'key2',
                'type'           => 'NUMERIC',
                'compare'        => 'EXISTS',
            )
        )
    );

You have to explicitly name each meta clause in order to sort by that clause

#3 @alexdelgado
3 years ago

Thanks @Funkatronic.

That makes sense, but it doesn't seem to be documented in the codex. I'll verify your approach and put in an update to the codex so someone else doesn't make the same mistake. Very strange that it sort of works without the extra keys..

#4 @Funkatronic
3 years ago

@alexdelgado It got changed just a few versions back. Still pretty new

#5 @netweb
3 years ago

  • Milestone Awaiting Review deleted
Note: See TracTickets for help on using tickets.