Advanced meta compare "like" in WP_Query

[sebastian.pisula]

I try get posts with LIKE compare. For example:

<?php
add_action( 'init', function () {
        $q = new WP_Query(
                [
                        'post_type'    => 'any',
                        'meta_key'     => '_stock_status',
                        'meta_value'   => 'i%st',
                        'meta_compare' => 'LIKE',
                ]
        );
} );

And I have SQL Query:

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_postmeta.meta_key = '_stock_status' AND wp_postmeta.meta_value LIKE '%i\\%st%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product') AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

I can't get posts with meta value for example instock and other matched.

[subrataemfluence]

WordPress automatically adds leading and trailing % to the text (value) passed via LIKE operator and escapes special characters (_, % and back slash (\)) if found inside the value passed.

In wp-includes\wp-db.php the following snippet does this automatically for us:

<?php
public function esc_like( $text ) {
   return addcslashes( $text, '_%\\' );
}

In your example, the value (i%st) you passed has a % sign in it hence WordPress automatically escapes it by invoking the above function with and converts it to %i\\%st%. Look how WordPress has automatically added one leading and one trailing % sings to your actual value.

My patch will prevent WordPress from escaping % sign inside a value (like yours) and will output your query to

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_postmeta.meta_key = '_stock_status' AND wp_postmeta.meta_value LIKE '%i%st%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product') AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Now it should return your desired result.

[subrataemfluence]

Prevents WordPress from escaping % sign in meta_value's value