Filter search query to exclude postmeta value

Question

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