[wp-trac] [WordPress Trac] #36652: Use meta_value in a meta query to decide type format in SQL clause

[WordPress Trac]

#36652: Use meta_value in a meta query to decide type format in SQL clause
-------------------------+------------------------------
 Reporter:  ericlewis    |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Awaiting Review
Component:  Query        |     Version:
 Severity:  normal       |  Resolution:
 Keywords:               |     Focuses:
-------------------------+------------------------------
Description changed by ericlewis:

Old description:

> The SQL clause generated for a meta query
> [https://github.com/WordPress/WordPress/blob/4.5/wp-includes/class-wp-
> meta-query.php#L628 quotes the `meta_value` in a string].
>
> This means that if there's a post with a postmeta field for likes set to
> 2 and you run the query looking for posts with 10 or more likes
>
> {{{
> #!php
> <?php
> $query = new WP_Query( array(
>         'meta_query' => array(
>                         array(
>                                 'key' => 'likes',
>                                 'value' => 10,
>                                 'compare' => '>='
>                         )
>                 )
> ) );
> }}}
>
> the query will return the post with 2 likes. This is because the SQL is
> doing a string comparison, as both the column value and the compared-to
> value are strings.
>
> The fix for the developer is to supply a `type` parameter like `NUMERIC`
> in the meta query clause which coerces a numeric MySQL comparison.
>
> We could use the meta_value's type to decide the type format the value
> takes in the SQL clause, so that a query like this works as expected
> without the `type` parameter.
>
> This was [https://core.trac.wordpress.org/ticket/27272#comment:13
> suggested] by @boone in #27272.

New description:

 The SQL clause generated for a meta query
 [https://github.com/WordPress/WordPress/blob/4.5/wp-includes/class-wp-
 meta-query.php#L628 quotes the `meta_value` in a string].

 This means that if there's a post with a postmeta field for likes set to 2
 and you run the query looking for posts with 10 or more likes

 {{{
 #!php
 <?php
 $query = new WP_Query( array(
         'meta_query' => array(
                 array(
                         'key' => 'likes',
                         'value' => 10,
                         'compare' => '>='
                 )
         )
 ) );
 }}}

 the query will return the post with 2 likes. This is because the SQL is
 doing a string comparison, as both the column value and the compared-to
 value are strings.

 The fix for the developer is to supply a `type` parameter like `NUMERIC`
 in the meta query clause which coerces a numeric MySQL comparison.

 We could use the meta_value's type to decide the type format the value
 takes in the SQL clause, so that a query like this works as expected
 without the `type` parameter.

 This was [https://core.trac.wordpress.org/ticket/27272#comment:13
 suggested] by @boone in #27272.

--

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36652#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform