Order By Multiple Meta Keys doesn't work as expected

[alexdelgado]

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.

[Funkatronic]

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

[alexdelgado]

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

[Funkatronic]

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