[wp-trac] [WordPress Trac] #21621: setting meta_type doesn't CAST orderby value

[WordPress Trac]

#21621: setting meta_type doesn't CAST orderby value
----------------------------+-----------------------------
 Reporter:  wonderboymusic  |      Owner:
     Type:  defect (bug)    |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Query           |    Version:  3.2
 Severity:  normal          |   Keywords:  has-patch
----------------------------+-----------------------------
 Setting {{{meta_type}}} in {{{WP_Query}}} will cast the {{{meta_value}}}
 in WHERE clauses to whatever {{{Meta_Query}}} internally resolves it as,
 however it does not create an alias and does not order by that alias. It
 orders by an un-CAST'd {{{meta_value}}} which doesn't work for numbers. I
 realize that {{{orderby}}} set to {{{meta_value_num}}} will fix this, but
 that doesn't change the fact that {{{meta_type}}} is being ignored.

 To test:

 {{{
 update_post_meta( 1, 'num_as_longtext', 123 );
 update_post_meta( 2, 'num_as_longtext', 99 );

 add_filter( 'query', function ( $sql ) { error_log( $sql ); return $sql; }
 );
 $stuff = new WP_Query( array(
         'fields' => 'ids',
         'post_type' => 'any',
         'meta_key' => 'num_as_longtext',
         'meta_value' => '0',
         'meta_compare' => '>',
         'meta_type' => 'UNSIGNED',
         'orderby' => 'meta_value',
         'order' => 'ASC'
 ) );

 print_r( $stuff->posts );

 exit();
 }}}

 That should return 2 then 1, it returns 1 then 2. It generates this SQL:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE 1=1  AND wp_posts.post_type IN ('post', 'page', 'attachment') AND
 (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key =
 'num_as_longtext' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0') )
 GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 10
 }}}

 My patch returns:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE 1=1  AND wp_posts.post_type IN ('post', 'page', 'attachment') AND
 (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key =
 'num_as_longtext' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0') )
 GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS UNSIGNED) ASC
 LIMIT 0, 10
 }}}

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/21621>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software