Using a pre_get_posts filter to search for multiple strings on all meta values

Question

Using a pre_get_posts filter to search a given string on all meta_values:

$query->set('meta_value', 'first string');

debug:

AND ( (CAST(wp_postmeta.meta_value AS CHAR) = 'first string') )

Works Ok :) but how can I search on all meta_values for an array of strings?

For example. I need something like:

$query->set('meta_value', array('first string', 'second string'));

AND ( (CAST(wp_postmeta.meta_value AS CHAR) = 'first string') OR (CAST(wp_postmeta.meta_value AS CHAR) = 'second string') )

It is possible using $query->set('meta_value'... or something else?

Thanks in advance!

Answer 1

I made some fixes to this code as it doesn't work anymore.

The following code will search the terms to be LIKE the title OR the content OR the meta_value :

function custom_search_where( $where ) {
    if ( is_search() ) {
        global $wpdb;

        // Overwrite the existing WHERE clause.
        $where = '';

        // Store all search terms into array.
        $search_terms = explode( ' ', get_search_query() );

        // Tables names.
        $type       = $wpdb->prefix . "posts.post_type";
        $status     = $wpdb->prefix . "posts.post_status";
        $title      = $wpdb->prefix . "posts.post_title";
        $content    = $wpdb->prefix . "posts.post_content";
        $meta_value = $wpdb->prefix . "postmeta.meta_value";

        foreach ( $search_terms as $term ) {
            $term = trim($term);
            $where .= " AND ( ($title LIKE '%$term%') OR ($content LIKE '%$term%') OR ($meta_value LIKE '%$term%') ) ";
        }

        // As WHERE clause is overwritten, you'll need to specify the post type, the status and/or anything else you need.
        // Post Types.
        $where .= " AND ($type IN ('post', 'page', ... )) ";
        // Post status.
        $where .= " AND ($status = 'publish') ";
    }
    return $where;
}
add_filter('posts_where', 'custom_search_where', 999);

and

function custom_search_join ($join) {
    if( is_search() ) {
        global $wpdb;
        $join .= " INNER JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id)";
    }
    return $join;
}
add_filter('posts_join', 'custom_search_join' );

Answer 2

Yes, I got it:

function custom_search_where( $where = '' ) {

    global $wpdb;

    if ( is_search() ) {

        if ( isset($_GET['my-terms']) ) {

            $count = count($_GET['my-terms']);

            if ( $count > 0 ) {

                foreach ( $_GET['my-terms'] as $term ) {

                    // http://codex.wordpress.org/Class_Reference/wpdb

                    $title      = $wpdb->prefix . "posts.post_title";
                    $content    = $wpdb->prefix . "posts.post_content";
                    $meta_value = $wpdb->prefix . "postmeta.meta_value";

                    $term       = trim($term);
                    $my_term    = $wpdb->escape($term);

                    $where .= " AND (($title LIKE '%$my_term%') OR ($content LIKE '%$my_term%') OR (CAST($meta_value AS CHAR) = '$my_term')) ";      

                }

            }

        }

    }

    return $where;

}


add_filter('posts_where', 'custom_search_where', 999);

and:

function custom_search_join ($join) {

    global $wpdb;

    if( is_search() ) {

        $existCustomTerms = false;

        if ( isset($_GET['my-terms']) ) {

            $count = count($_GET['my-terms']);

            if ( $count > 0 ) {

                $join .= "INNER JOIN $table ON ($postID = $postmetaID)";

            }

        }

    }

    return $join;

}


add_filter('posts_join', 'custom_search_join' );