I’ve figured out a fix.
First, to fix bad results for an empty search, I require that
(!empty(get_query_var(‘s’))
Before setting the “meta_query” query variable in my plugin.
I thought the fix would be with the posts_request filter, but it continued to replace
select SQL_CALC_GET_ROWS wp_posts.ID
with
select SQL_CALC_GET_ROWS wp_posts.*
Even when I replaced the * with “ID” in my filter, it wouldn’t return correct results. I have a feeling that I could find why this is if I looked further in the code, but I’m on a deadline so I didn’t bother.
Instead, I realized that the correct filter is posts_request_ids, called on line 2819 of class-wp-query.php in WordPress 4.9.8, immediately before queries are sent. With that change, it took no further modifications to make the code work.
The custom filter is as follows:
/**
* FIX QUERIES
*
* Adds metadata "where" conditions to the "where" conditions of the main query,
* by shifting a few parentheses.
*
* @param string $request The SQL query as a string
* @param object $query_instance The user's search as string
* @return string $request
* @author Andrew MacGillivray
*/
function fixQuery($request, $query_instance) {
if (!empty(get_query_var('s'))) {
// remove line breaks so stripos and substr_replace will work
$request = trim(preg_replace('/\s+/', ' ', $request));
// If you're copying this, remember to replace "wp_" with your table prefix.
// Or rewrite to use $wpdb...
$replace = array(
"))) OR ( ( wp_postmeta.meta_key" => array(
"))) OR ( (" => ") ) OR ( ( ",
),
") ) AND wp_posts.post_type IN (" => ") ) ) AND wp_posts.post_type IN (",
);
foreach ($replace as $key => $value) {
$pos = stripos($request, $key);
if (is_array($value)) {
foreach ($value as $orig => $replace) {
$request = substr_replace($request, $replace, $pos, strlen($orig));
}
} else {
$request = substr_replace($request, $value, $pos, strlen($key));
}
}
}
return $request;
}
add_filter('posts_request_ids', 'fixQuery', 10, 2);
In that code we are including the “OR … metadata LIKE ‘%user-query%'” in the same “AND” clause as the primary query. The above filter looks for the “OR” clause added by the following filter:
/**
* In Global WP_Query, replace first "And" with "OR" to allow meta-queries (when present)
* to function properly.
*
* @see https://stackoverflow.com/questions/43734845/how-to-give-or-relation-in-tax-query-and-meta-query-wordpress
* @author StackOverflow: Naji Amer, 2018-02-09
*/
function filter_meta_query($sql){
if (!(is_admin()) && (is_search()) && !empty(get_query_var('s'))) {
$pos = strpos($sql['where'], 'AND');
if ($pos !== false) {
$sql['where'] = substr_replace($sql['where'], 'OR', $pos, strlen('AND'));
}
}
return $sql;
}
add_filter('get_meta_sql', 'filter_meta_query', 10, 1);
Adding those two filters (and modifying my plugins so that meta queries are only added when the search query isn’t empty) makes everything work as desired.
Hopefully this can help someone else.