[wp-trac] [WordPress Trac] #38442: Error when WP_Query parses orderby array in function parse_order

[WordPress Trac]

#38442: Error when WP_Query parses orderby array in function parse_order
----------------------------+-----------------------------
 Reporter:  oloynet         |      Owner:
     Type:  defect (bug)    |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Filesystem API  |    Version:  4.6.1
 Severity:  normal          |   Keywords:
  Focuses:                  |
----------------------------+-----------------------------
 Example of query where I want to order by two custom fields in meta query
 '''start_date_order''' and '''is_sticky'''

 {{{#!php
 <?php
 $args = array(
     'no_found_rows'  => true ,
     'posts_per_page' => 4,
     'post_type'      => 'event',
     'post_status'    => 'publish',

     'meta_query' => array(
         'relation' => 'AND',
         array(
             'key'  => 'start_date_order',
             'type' => 'UNSIGNED',
         ),
         array(
             'key'  => 'is_sticky',
             'type' => 'UNSIGNED',
         ),
     ),
     'orderby' => array(
         'start_date_order' => 'DESC',
         'is_sticky'        => 'ASC',
     ),
 );

 $result_query = new WP_Query( $args );

 echo $result_query->request;
 }}}

 The wrong SQL query generated is

 {{{
 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 = 'start_date_order'
     AND mt1.meta_key = 'is_sticky'
 )
 AND wp_posts.post_type = 'event'
 AND ((wp_posts.post_status = 'publish'))
 GROUP BY wp_posts.ID
 ORDER BY CAST(wp_postmeta.meta_value AS UNSIGNED) DESC
 LIMIT 0, 3
 }}}


 The '$primary_meta_query' var in method parse_order( $order ) is set
 forever with the first item of '$meta_clauses' array

 see line 2336 /wp-includes/query.php
 {{{#!php
 <?php
 primary_meta_query = reset( $meta_clauses );
 }}}


 I quickly fix the problem with the following PHP code. Could use a native
 array function from PHP.

 {{{#!php
 <?php
 //$primary_meta_query = reset( $meta_clauses );

 $primary_meta_query = array();
 foreach( $meta_clauses as $meta_clause ) {
     if( $meta_clause['key'] == $orderby ) {
         $primary_meta_query = $meta_clause;
         break;
     }
 }
 }}}


 Now the good SQL query is generated with two columns for ordering:

 {{{
 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 = 'start_date_order'
     AND mt1.meta_key = 'is_sticky'
 )
 AND wp_posts.post_type = 'event'
 AND ((wp_posts.post_status = 'publish'))
 GROUP BY wp_posts.ID
 ORDER BY CAST(wp_postmeta.meta_value AS UNSIGNED) DESC,
 CAST(mt1.meta_value AS UNSIGNED) ASC
 LIMIT 0, 3
 }}}

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