[wp-trac] [WordPress Trac] #36937: Order By Multiple meta keys doesn't work as expected

[WordPress Trac]

#36937: Order By Multiple meta keys doesn't work as expected
--------------------------+-----------------------------
 Reporter:  alexdelgado   |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  4.5.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36937>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform