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