0

In my wordpress site I am making use of the pre_get_posts filter to customise my site search query. In functions file I have the following below.

Now my issue is, using FieldManager I have added a custom meta field called hide_from_search_results which is now included in my wp_postmeta table, with either a 0 or 1 value. Not all posts/pages will have this field set however. I need to add the condition to not show anything that has this set to 1.

First I have joined the wp_posts and wp_postmeta tables like so:

function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() && !is_admin() ) {
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

I then group the results by the post ID:

function cf_posts_groupby($groupby) {
    global $wpdb;

    if (is_search() && !is_admin()) {
        return $wpdb->posts . '.' . 'ID';
    }

    return $groupby;
}
add_filter( 'posts_groupby', 'cf_posts_groupby' );

Modify the search query with posts_where for things like excluding images from the results:

function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() && !is_admin() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_value LIKE $1)", $where );
        
        $where .= ' AND ' . $wpdb->posts . '.post_mime_type != "image/jpeg"';
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

I lastly then use pre_get_posts to alter the query to include certain post types and only showing published:

function cf_posts_query( $query ) {
    if ( is_search() && !is_admin() ) {

        if (isset($_GET['post_types'])) {
            $query->set('post_type', $_GET['post_types']);
        } else {
            $query->set('post_type', ['page']);
        }

        $query->set( 'post_status', array( 'publish', 'inherit' ) );
      
    }

   return $query;
}
add_filter( 'pre_get_posts', 'cf_posts_query' );

I have tried adding the following into my pre_get_posts

    $query->set( 'meta_query', array(
        array(
          'key' => 'hide_from_search_results',
          'compare' => '!=',
          'value' => '1'
        )
      ));

But get the following error:

WordPress database error: [Not unique table/alias: 'wp_postmeta']
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 AND wp_posts.ID NOT IN (4440,4436) AND (((wp_posts.post_title LIKE '%coron%') OR (wp_postmeta.meta_value LIKE '%coron%') OR (wp_posts.post_excerpt LIKE '%coron%') OR (wp_posts.post_content LIKE '%coron%'))) AND ( ( wp_postmeta.meta_key = 'hide_from_search_results' AND wp_postmeta.meta_value != '1' ) ) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) AND wp_posts.post_mime_type != "image/jpeg" GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%coron%' DESC, wp_posts.post_date DESC LIMIT 0, 20

WordPress database error: [Not unique table/alias: 'wp_postmeta']
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 AND wp_posts.post_name IN ('search') AND wp_posts.ID NOT IN (4440,4436) AND ( 0 = 1 ) AND ( ( wp_postmeta.meta_key = 'hide_from_search_results' AND wp_postmeta.meta_value != '1' ) ) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) AND wp_posts.post_mime_type != "image/jpeg" GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

Alternatively I have tried adding this whithin my posts_where function

$where .= ' AND (' . $wpdb->postmeta . '.meta_key != "hide_from_search_results") OR (' . $wpdb->postmeta . '.meta_key == "hide_from_search_results" AND ' . $wpdb->postmeta . '.meta_value == "1")';

But get:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== "hide_from_search_results" AND wp_postmeta.meta_value == "1") AND wp_posts.po' at line 1]
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 AND wp_posts.ID NOT IN (4440,4436) AND (((wp_posts.post_title LIKE '%coron%') OR (wp_postmeta.meta_value LIKE '%coron%') OR (wp_posts.post_excerpt LIKE '%coron%') OR (wp_posts.post_content LIKE '%coron%'))) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) AND (wp_postmeta.meta_key != "hide_from_search_results") OR (wp_postmeta.meta_key == "hide_from_search_results" AND wp_postmeta.meta_value == "1") AND wp_posts.post_mime_type != "image/jpeg" GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%coron%' DESC, wp_posts.post_date DESC LIMIT 0, 20

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== "hide_from_search_results" AND wp_postmeta.meta_value == "1") AND wp_posts.po' at line 3]
SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 AND wp_posts.post_name IN ('search') AND wp_posts.ID NOT IN (4440,4436) AND ( 0 = 1 ) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) AND (wp_postmeta.meta_key != "hide_from_search_results") OR (wp_postmeta.meta_key == "hide_from_search_results" AND wp_postmeta.meta_value == "1") AND wp_posts.post_mime_type != "image/jpeg" GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
1
  • 1
    You shouldn't use raw SQL filters to do this, WP_Querry already supports this ability out of the box. One thing to keep in mind though is that you always want to ask the database for what you want, never what you don't want. Excluding things, NOT IN etc filtering out things is very expensive and slow, especially for a table that isn't designed for filtering such as post meta. You would have much more performance with a show_in_search_results post meta, even greater performance if it was a term in a taxonomy. Otherwise the DB needs to create a temporary table that's missing those things
    – Tom J Nowell
    Aug 31, 2021 at 12:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.