Issue with SQL query generated by pre_get_posts

[greendemiurge]

For reference, I attempted to run this by support first to validate that the issue is truly a bug, but I have not yet gotten a reply. I have vetted this extensively myself, I was only looking for a second opinion (​http://wordpress.org/support/topic/problem-with-the-wp-meta_query-sql?replies=2).

The issue, in brief, is that when I use the pre_get_posts method to tell Wordpress to search two custom meta fields in addition to the Title and Content fields the resulting SQL Query is put together using AND conditions rather than OR conditions, meaning that the searched keyword has to exist in all places for a result to be returned, instead of being returned if the keyword is in any of the fields. I have verified this is the case using xDebug and by setting the keyword in all places.

Here is the code I used in functions.php to add the two custom meta fields:

function custom_search_query( $query ) {
	if ( !is_admin() && $query->is_search ) {
		$query->set('post_type', 'listing');

		$metaquery = array(
			array(
				'key' => '_shortdescription',
				'value' => $query->query_vars['s'],
				'compare' => 'LIKE'
			),
			array(
				'key' => '_longdescription',
				'value' => $query->query_vars['s'],
				'compare' => 'LIKE'
			));

		$query->set('meta_query', $metaquery);

	}
     }
     add_filter( 'pre_get_posts', 'custom_search_query');

And here is the SQL query this code generates when searching for "val":

SELECT SQL_CALC_FOUND_ROWS  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_posts.post_title LIKE '%val%')
OR (wp_posts.post_content LIKE '%val%')))
AND (wp_posts.post_password = '')
AND wp_posts.post_type = 'listing'
AND (wp_posts.post_status = 'publish')
AND ((wp_postmeta.meta_key = '_shortdescription' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
AND (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR) LIKE '%val%') )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC LIMIT 0, 10

This query functions as expected if I manually run it, changing the two final ANDs to ORs:

SELECT SQL_CALC_FOUND_ROWS  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_posts.post_title LIKE '%val%')
OR (wp_posts.post_content LIKE '%val%')))
AND (wp_posts.post_password = '')
AND wp_posts.post_type = 'listing'
AND (wp_posts.post_status = 'publish')
OR ((wp_postmeta.meta_key = '_shortdescription' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
OR (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR) LIKE '%val%') )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC LIMIT 0, 10

I pulled the first defective query from Wordpress using Eclipse and xDebug, setting a breakpoint just prior to the loop code on search.php.

Thank you for your consideration

[SergeyBiryukov]

Note that you can add 'relation' => 'OR' to your meta query to get this:

... meta_key = '_shortdescription' ... OR ... meta_key = '_longdescription'

However, looks like there's no way to change the AND before the meta conditions using just meta_query.

[boonebgorges]

Thanks for the detailed report.

However, looks like there's no way to change the AND before the meta conditions using just meta_query.

Right. meta_query is acting properly here - the default relation is AND. The larger issue is that all conditions in WP_Query - meta_query, tax_query, postin, authorin, date_query, post_status, post_type, etc etc etc - are linked together using AND logic. There is simply no way, using WP_Query, to say that you want posts that EITHER match a certain search term OR a certain meta_query OR a certain postin, much less more complex groupings of these.

It would be neat to be able to do this, but it'd require a rewrite of much of the class, as well as a good deal of up-front architectural work to decide on a syntax, maintain backward compatibility, etc.

Maybe we should try to do this one day, but not today.