I've seen several questions about including the content of meta-fields in a wordpress search. My question is not a duplicate of these.
The issue I'm having stems from how meta queries are appended to searches. Since the query is attached to the global query - rather than being a query for metabox values alone - it's joined by an AND operator.
For the security-conscious -- note that an identical nonce surrounds the user's query in each "LIKE 'user-input'" statement, but the nonces have been removed for legibility.
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'stc_make' )
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B' ) OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
)
)
AND
(
( wp_postmeta.meta_key = 'stc_make' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_models' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_desc' AND wp_postmeta.meta_value LIKE 'ADS-B' )
)
AND wp_posts.post_type IN (
'post',
'page',
'attachment',
'employees', /* custom post type */
'certificates' /* custom post type */
)
AND
(
wp_posts.post_status = 'publish' OR
wp_posts.post_author = 1 AND wp_posts.post_status = 'private'
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title LIKE 'ADS-B' DESC, wp_posts.post_date DESC LIMIT 0, 10
This effectively prevented the search from including posts from custom post types even if the query matches content in metaboxes.
I solved this with a solution from Naji Amer on stackexchange:
function filter_meta_query($sql){
if (!(is_admin()) && (is_search())) {
$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);
(With the addition of the condition (!(is_admin()), because otherwise this will prevent you from being able to change your menu items in the admin menu...)
The code changes the "AND" (before the conditions for the content of the metaboxes) to an OR. This works, but only if there is a query 's'. When there isn't a query, the first AND statement that defines the standard wordpress query:
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B') OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
)
)
Is removed. Which gives us a problem, because the remaining query has
WHERE 1=1 OR ... (conditions)
In other words, where true = true OR the other conditions return true. The query returns all post types in the wp_posts table.
I've done some digging and found the following on lines 2726 and 2809 of class-wp-query.php, respectively:
$this->request = $old_request = "SELECT $found_rows $distinct $fields FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits";
(line 2726)
$this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits";
(line 2809)
As far as I can tell, WHERE 1=1 is hard coded.
My current wordpress project includes a couple of custom post types that are meant to be publicly searchable ('employees' and 'certificates'). I would like to use the plugin to allow these to be found by the content of their metaboxes in the global $wp_query.
To do so, I've added a function to each class that uses the standard
set_query_var('meta_query', array(...))
to define the metaboxes that should be searched.
I would like to preserve my ability to add or remove the plugins / post types at any time, which means that using a custom SQL string is not desirable (unless I write my own function in the theme's functions.php to generate it).
However, using the default query generator is giving me problems because of the "where 1=1" clause.
So far, I haven't found a hook late enough to take the final query and modify it before it's sent (my function to change "AND" to "OR" is modifying a meta query object rather than the query string).
And I haven't found a way to group the meta query parameters in with the default wordpress search parameters, which would something look like so:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'stc_make' )
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B' ) OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
) OR (
( wp_postmeta.meta_key = 'stc_make' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_models' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_desc' AND wp_postmeta.meta_value LIKE 'ADS-B' )
)
)
...
I thought that I would be able to fix the problem by detecting when $wp_query->s wasn't empty, but it looks like even a defined query will still return unwanted post types when they match the search string (e.g. nav-menu-items showing up in searches, so long as their title matches the search, even though they don't comply with the
IN ([... post types])
parameter in the query).
Am I going about this all wrong? Is there a simpler way to make a query in the format I want, or should I be generating multiple queries and merging the results? (And in the case of the latter, how do I attach the results to the default search without requiring the theme to know something about the plugins?)
Let me know if I can clarify anything any further.
I've seen several questions about including the content of meta-fields in a wordpress search. My question is not a duplicate of these.
The issue I'm having stems from how meta queries are appended to searches. Since the query is attached to the global query - rather than being a query for metabox values alone - it's joined by an AND operator.
For the security-conscious -- note that an identical nonce surrounds the user's query in each "LIKE 'user-input'" statement, but the nonces have been removed for legibility.
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'stc_make' )
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B' ) OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
)
)
AND
(
( wp_postmeta.meta_key = 'stc_make' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_models' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_desc' AND wp_postmeta.meta_value LIKE 'ADS-B' )
)
AND wp_posts.post_type IN (
'post',
'page',
'attachment',
'employees', /* custom post type */
'certificates' /* custom post type */
)
AND
(
wp_posts.post_status = 'publish' OR
wp_posts.post_author = 1 AND wp_posts.post_status = 'private'
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title LIKE 'ADS-B' DESC, wp_posts.post_date DESC LIMIT 0, 10
This effectively prevented the search from including posts from custom post types even if the query matches content in metaboxes.
I solved this with a solution from Naji Amer on stackexchange:
function filter_meta_query($sql){
if (!(is_admin()) && (is_search())) {
$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);
(With the addition of the condition (!(is_admin()), because otherwise this will prevent you from being able to change your menu items in the admin menu...)
The code changes the "AND" (before the conditions for the content of the metaboxes) to an OR. This works, but only if there is a query 's'. When there isn't a query, the first AND statement that defines the standard wordpress query:
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B') OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
)
)
Is removed. Which gives us a problem, because the remaining query has
WHERE 1=1 OR ... (conditions)
In other words, where true = true OR the other conditions return true. The query returns all post types in the wp_posts table.
I've done some digging and found the following on lines 2726 and 2809 of class-wp-query.php, respectively:
$this->request = $old_request = "SELECT $found_rows $distinct $fields FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits";
(line 2726)
$this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits";
(line 2809)
As far as I can tell, WHERE 1=1 is hard coded.
My current wordpress project includes a couple of custom post types that are meant to be publicly searchable ('employees' and 'certificates'). I would like to use the plugin to allow these to be found by the content of their metaboxes in the global $wp_query.
To do so, I've added a function to each class that uses the standard
set_query_var('meta_query', array(...))
to define the metaboxes that should be searched.
I would like to preserve my ability to add or remove the plugins / post types at any time, which means that using a custom SQL string is not desirable (unless I write my own function in the theme's functions.php to generate it).
However, using the default query generator is giving me problems because of the "where 1=1" clause.
So far, I haven't found a hook late enough to take the final query and modify it before it's sent (my function to change "AND" to "OR" is modifying a meta query object rather than the query string).
And I haven't found a way to group the meta query parameters in with the default wordpress search parameters, which would something look like so:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'stc_make' )
WHERE 1=1
AND
(
(
( wp_posts.post_title LIKE 'ADS-B' ) OR
( wp_posts.post_excerpt LIKE 'ADS-B' ) OR
( wp_posts.post_content LIKE 'ADS-B' )
) OR (
( wp_postmeta.meta_key = 'stc_make' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_models' AND wp_postmeta.meta_value LIKE 'ADS-B' ) OR
( wp_postmeta.meta_key = 'stc_desc' AND wp_postmeta.meta_value LIKE 'ADS-B' )
)
)
...
I thought that I would be able to fix the problem by detecting when $wp_query->s wasn't empty, but it looks like even a defined query will still return unwanted post types when they match the search string (e.g. nav-menu-items showing up in searches, so long as their title matches the search, even though they don't comply with the
IN ([... post types])
parameter in the query).
Am I going about this all wrong? Is there a simpler way to make a query in the format I want, or should I be generating multiple queries and merging the results? (And in the case of the latter, how do I attach the results to the default search without requiring the theme to know something about the plugins?)
Let me know if I can clarify anything any further.
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/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.