32

Trying to build a search that not only searches the defaults (title, content etc) but also a specific custom field.

My current query:

$args = array(
  'post_type' => 'post',
  's' => $query,
  'meta_query' => array(
     array(
       'key' => 'speel',
       'value' => $query,
       'compare' => 'LIKE'
     )
   )
);

$search = new WP_Query( $args )
...

This returns posts which match both the search query AND the meta query, but I would also like it to also return posts where it simply matches either one of them.

Any ideas?

1
  • What you are wanting to do isn't possible using just one search query. You would need to run both queries separately and then merge them together. This has been described at this other answer. It should give you a hand with how to do it. wordpress.stackexchange.com/questions/55519/… – Nick Perkins Jan 8 '13 at 4:31

12 Answers 12

22

I have been searching for hours for a solution to this problem. Array merging is not the way to go, especially when the queries are complex and you must be able to add to meta queries in the future. The solution which is simplistically beautiful is to change 's' to one which allows both searching titles and meta fields.

add_action( 'pre_get_posts', function( $q )
{
    if( $title = $q->get( '_meta_or_title' ) )
    {
        add_filter( 'get_meta_sql', function( $sql ) use ( $title )
        {
            global $wpdb;

            // Only run once:
            static $nr = 0; 
            if( 0 != $nr++ ) return $sql;

            // Modified WHERE
            $sql['where'] = sprintf(
                " AND ( %s OR %s ) ",
                $wpdb->prepare( "{$wpdb->posts}.post_title like '%%%s%%'", $title),
                mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
            );

            return $sql;
        });
    }
});

Usage:

$meta_query = array();
$args = array();
$search_string = "test";

$meta_query[] = array(
    'key' => 'staff_name',
    'value' => $search_string,
    'compare' => 'LIKE'
);
$meta_query[] = array(
    'key' => 'staff_email',
    'value' => $search_string,
    'compare' => 'LIKE'
);

//if there is more than one meta query 'or' them
if(count($meta_query) > 1) {
    $meta_query['relation'] = 'OR';
}

// The Query
$args['post_type'] = "staff";
$args['_meta_or_title'] = $search_string; //not using 's' anymore
$args['meta_query'] = $meta_query;



$the_query = new WP_Query($args)
4
  • this is the right way, – Zorox Aug 12 '16 at 17:20
  • Fantastic, this worked very well for me. Great solution! Thanks! – Fabiano Sep 23 '17 at 15:56
  • This is a query, not a search. If you have plugins that work on search, it doesn't work. Am I wrong? – marek.m Oct 30 '17 at 9:57
  • 1
    @marek.m you would need to customize the plugin (perhaps using a filter if its available) to add the meta query. – FooBar Jan 21 '20 at 20:09
7

A lot of code can be reduced by using a modified version of this answer.

$q1 = new WP_Query( array(
    'post_type' => 'post',
    'posts_per_page' => -1,
    's' => $query
));

$q2 = new WP_Query( array(
    'post_type' => 'post',
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
           'key' => 'speel',
           'value' => $query,
           'compare' => 'LIKE'
        )
     )
));

$result = new WP_Query();
$result->posts = array_unique( array_merge( $q1->posts, $q2->posts ), SORT_REGULAR );
$result->post_count = count( $result->posts );
1
  • This worked great for me (especially since I needed to use WP_Query instead of get_posts). I did have to modify your post_count line to be: $result->post_count = count( $result->posts ); because I was only getting 1 result otherwise. – GreatBlakes May 27 '15 at 13:21
5

i had the same problem, for my new site i just added a new meta "title" :

functions.php

add_action('save_post', 'title_to_meta');

function title_to_meta($post_id)
{
    update_post_meta($post_id, 'title', get_the_title($post_id)); 
}

And then.. just add something like that :

$sub = array('relation' => 'OR');

$sub[] = array(
    'key'     => 'tags',
    'value'   => $_POST['q'],
    'compare' => 'LIKE',
);

$sub[] = array(
    'key'     => 'description',
    'value'   => $_POST['q'],
    'compare' => 'LIKE',
);

$sub[] = array(
    'key'     => 'title',
    'value'   => $_POST['q'],
    'compare' => 'LIKE',
);

$params['meta_query'] = $sub;

What do you think about this workaround ?

3
  • 1
    That's actually not bad, but requires you to either re-save all existing posts or start using it before you start adding content. – Berend Dec 6 '16 at 11:55
  • @Berend you could probably write a function that gets all posts and loops through them updating the post_meta for each. Include it in a custom template or function, run it once, then discard it. – Slam May 23 '18 at 6:13
  • Best response ever : no manual SQL transformations, no extra visible data... Thank you very much ! – jeanmatthieud Nov 19 '20 at 10:22
4

I have optimized @Stabir Kira answer a bit

function wp78649_extend_search( $query ) {
    $search_term = filter_input( INPUT_GET, 's', FILTER_SANITIZE_NUMBER_INT) ?: 0;
    if (
        $query->is_search
        && !is_admin()
        && $query->is_main_query()
        && //your extra condition
    ) {
        $query->set('meta_query', [
            [
                'key' => 'meta_key',
                'value' => $search_term,
                'compare' => '='
            ]
        ]);

        add_filter( 'get_meta_sql', function( $sql )
        {
            global $wpdb;

            static $nr = 0;
            if( 0 != $nr++ ) return $sql;

            $sql['where'] = mb_eregi_replace( '^ AND', ' OR', $sql['where']);

            return $sql;
        });
    }
    return $query;
}
add_action( 'pre_get_posts', 'wp78649_extend_search');

Now you can search by (title, content, excrept) or (meta field) or (both of them).

3

As per Nick Perkins' suggestion, I had to merge two queries like so:

$q1 = get_posts(array(
        'fields' => 'ids',
        'post_type' => 'post',
        's' => $query
));

$q2 = get_posts(array(
        'fields' => 'ids',
        'post_type' => 'post',
        'meta_query' => array(
            array(
               'key' => 'speel',
               'value' => $query,
               'compare' => 'LIKE'
            )
         )
));

$unique = array_unique( array_merge( $q1, $q2 ) );

$posts = get_posts(array(
    'post_type' => 'posts',
    'post__in' => $unique,
    'post_status' => 'publish',
    'posts_per_page' => -1
));

if( $posts ) : foreach( $posts as $post ) :
     setup_postdata($post);

     // now use standard loop functions like the_title() etc.     

enforeach; endif;
2
  • 1
    Is this still not possible without merging in 2016? I'm editing search query via pre_get_posts, so this is really not an option ... – trainoasis Feb 9 '16 at 8:24
  • @trainoasis I don't think so. I have been trying it since last 2 hours and a google search got me here. – Umair Khan Jadoon Nov 5 '16 at 10:40
2

Well its kind of a hack but it works. You need to add posts_clauses filter. This filter function check for the any of the query word exists in the custom field "speel" and the remaining query stays intact.

function custom_search_where($pieces) {

    // filter for your query
    if (is_search() && !is_admin()) {

        global $wpdb;

        $keywords = explode(' ', get_query_var('s'));
        $query = "";
        foreach ($keywords as $word) {

            // skip possible adverbs and numbers
            if (is_numeric($word) || strlen($word) <= 2) 
                continue;

            $query .= "((mypm1.meta_key = 'speel')";
            $query .= " AND (mypm1.meta_value  LIKE '%{$word}%')) OR ";
        }

        if (!empty($query)) {
            // add to where clause
            $pieces['where'] = str_replace("(((wp_posts.post_title LIKE '%", "( {$query} ((wp_posts.post_title LIKE '%", $pieces['where']);

            $pieces['join'] = $pieces['join'] . " INNER JOIN {$wpdb->postmeta} AS mypm1 ON ({$wpdb->posts}.ID = mypm1.post_id)";
        }
    }
    return ($pieces);
}
add_filter('posts_clauses', 'custom_search_where', 20, 1);
2

I couldn't find a solution to look for multiple keywords that can be mixed in either post title, description AND/OR one or several metas, so I made my own addition to the search function.

All you need is to add the following code in function.php, and whenever you use the 's' argument in a standard WP_Query() function and want it to search in one or several meta fields as well, you simply add a 's_meta_keys' argument that is an array of the meta(s) key(s) you want to search in:

/************************************************************************\
|**                                                                    **|
|**  Allow WP_Query() search function to look for multiple keywords    **|
|**  in metas in addition to post_title and post_content               **|
|**                                                                    **|
|**  By rAthus @ Arkanite                                              **|
|**  Created: 2020-08-18                                               **|
|**  Updated: 2020-08-19                                               **|
|**                                                                    **|
|**  Just use the usual 's' argument and add a 's_meta_keys' argument  **|
|**  containing an array of the meta(s) key you want to search in :)   **|
|**                                                                    **|
|**  Example :                                                         **|
|**                                                                    **|
|**  $args = array(                                                    **|
|**      'numberposts'  => -1,                                         **|
|**      'post_type' => 'post',                                        **|
|**      's' => $MY_SEARCH_STRING,                                     **|
|**      's_meta_keys' => array('META_KEY_1','META_KEY_2');            **|
|**      'orderby' => 'date',                                          **|
|**      'order'   => 'DESC',                                          **|
|**  );                                                                **|
|**  $posts = new WP_Query($args);                                     **|
|**                                                                    **|
\************************************************************************/
add_action('pre_get_posts', 'my_search_query'); // add the special search fonction on each get_posts query (this includes WP_Query())
function my_search_query($query) {
    if ($query->is_search() and $query->query_vars and $query->query_vars['s'] and $query->query_vars['s_meta_keys']) { // if we are searching using the 's' argument and added a 's_meta_keys' argument
        global $wpdb;
        $search = $query->query_vars['s']; // get the search string
        $ids = array(); // initiate array of martching post ids per searched keyword
        foreach (explode(' ',$search) as $term) { // explode keywords and look for matching results for each
            $term = trim($term); // remove unnecessary spaces
            if (!empty($term)) { // check the the keyword is not empty
                $query_posts = $wpdb->prepare("SELECT * FROM {$wpdb->posts} WHERE post_status='publish' AND ((post_title LIKE '%%%s%%') OR (post_content LIKE '%%%s%%'))", $term, $term); // search in title and content like the normal function does
                $ids_posts = [];
                $results = $wpdb->get_results($query_posts);
                if ($wpdb->last_error)
                    die($wpdb->last_error);
                foreach ($results as $result)
                    $ids_posts[] = $result->ID; // gather matching post ids
                $query_meta = [];
                foreach($query->query_vars['s_meta_keys'] as $meta_key) // now construct a search query the search in each desired meta key
                    $query_meta[] = $wpdb->prepare("meta_key='%s' AND meta_value LIKE '%%%s%%'", $meta_key, $term);
                $query_metas = $wpdb->prepare("SELECT * FROM {$wpdb->postmeta} WHERE ((".implode(') OR (',$query_meta)."))");
                $ids_metas = [];
                $results = $wpdb->get_results($query_metas);
                if ($wpdb->last_error)
                    die($wpdb->last_error);
                foreach ($results as $result)
                    $ids_metas[] = $result->post_id; // gather matching post ids
                $merged = array_merge($ids_posts,$ids_metas); // merge the title, content and meta ids resulting from both queries
                $unique = array_unique($merged); // remove duplicates
                if (!$unique)
                    $unique = array(0); // if no result, add a "0" id otherwise all posts wil lbe returned
                $ids[] = $unique; // add array of matching ids into the main array
            }
        }
        if (count($ids)>1)
            $intersected = call_user_func_array('array_intersect',$ids); // if several keywords keep only ids that are found in all keywords' matching arrays
        else
            $intersected = $ids[0]; // otherwise keep the single matching ids array
        $unique = array_unique($intersected); // remove duplicates
        if (!$unique)
            $unique = array(0); // if no result, add a "0" id otherwise all posts wil lbe returned
        unset($query->query_vars['s']); // unset normal search query
        $query->set('post__in',$unique); // add a filter by post id instead
    }
}

Example use:

$search= "kewords to search";

$args = array(
    'numberposts'   => -1,
    'post_type' => 'post',
    's' => $search,
    's_meta_keys' => array('short_desc','tags');
    'orderby' => 'date',
    'order'   => 'DESC',
);

$posts = new WP_Query($args);

This example will look for the keywords "kewords to search" in post titles, descriptions, and meta keys 'short_desc' and 'tags'.

Keywords can be found in one or several of the fileds, in any order, it will return any post that has all the keywords in any of the designated fields.

You can obiously force the search in a list of meta keys you include in the fonction and get rid of the extra agruments if you want ALL search queries to include these meta keys :)

Hope that will help anyone who face the same issue I did!

1

All of the above solutions only return results if a match exists in the speel meta key. If you have results elsewhere but not in this field you'll get nothing. Nobody wants that.

A left join is needed. The following will create one.

           $meta_query_args = array(
              'relation' => 'OR',
              array(
                'key' => 'speel',
                'value' => $search_term,
                'compare' => 'LIKE',
              ),
              array(
                'key' => 'speel',
                'compare' => 'NOT EXISTS',
              ),
            );
            $query->set('meta_query', $meta_query_args);
1

for me works perfect the next code:

            $search_word = $_GET['id'];
        $data['words'] = trim(urldecode($search_word));

        $q1 = new WP_Query( array(
            'post_type' => array('notas', 'productos'),
            'posts_per_page' => -1,
            's' => $search_word
        ));

        $q2 = new WP_Query( array(
            'post_type' => array('notas', 'productos'),
            'posts_per_page' => -1,
            'meta_query' => array(
                'relation' => 'OR',
                array(
                   'key'   => 'subtitulo',
                    'value' => $search_word,
                    'compare' => 'LIKE'
                ),
                array(
                    'key'   => 'thumbnail_bajada',
                    'value' => $search_word,
                    'compare' => 'LIKE'
                )
             )
        ));

        $result = new WP_Query();
        $result->posts = array_unique( array_merge( $q1->posts, $q2->posts ), SORT_REGULAR );
        $result->post_count = count( $result->posts );
0

This is a great solution but you need to fix one thing. When you call 'post__in' you need to set an array of ids and $unique is an array of posts.

example:

$q1 = get_posts(array(
        'fields' => 'ids',
        'post_type' => 'post',
        's' => $query
));

$q2 = get_posts(array(
        'fields' => 'ids',
        'post_type' => 'post',
        'meta_query' => array(
            array(
               'key' => 'speel',
               'value' => $query,
               'compare' => 'LIKE'
            )
         )
));

$unique = array_unique( array_merge( $q1->posts, $q2->posts ) );

$array = array(); //here you initialize your array

foreach($posts as $post)
{
    $array[] = $post->ID; //fill the array with post ID
}


$posts = get_posts(array(
    'post_type' => 'posts',
    'post__in' => $array,
    'post_status' => 'publish',
    'posts_per_page' => -1
));
0

@satbir-kira answer works great but it will only search through the meta and post title. If you want it to search through meta, title and content, here is the modified version.

    add_action( 'pre_get_posts', function( $q )
    {
      if( $title = $q->get( '_meta_or_title' ) )
      {
        add_filter( 'get_meta_sql', function( $sql ) use ( $title )
        {
          global $wpdb;

          // Only run once:
          static $nr = 0;
          if( 0 != $nr++ ) return $sql;

          // Modified WHERE
          $sql['where'] = sprintf(
              " AND ( (%s OR %s) OR %s ) ",
              $wpdb->prepare( "{$wpdb->posts}.post_title like '%%%s%%'", $title),
              $wpdb->prepare( "{$wpdb->posts}.post_content like '%%%s%%'", $title),
              mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
          );

          return $sql;
        });
      }
    });

And here is it's usage:

$args['_meta_or_title'] = $get['search']; //not using 's' anymore

$args['meta_query'] = array(
  'relation' => 'OR',
  array(
    'key' => '_ltc_org_name',
    'value' => $get['search'],
    'compare' => 'LIKE'
  ),
  array(
    'key' => '_ltc_org_school',
    'value' => $get['search'],
    'compare' => 'LIKE'
  ),
  array(
    'key' => '_ltc_district_address',
    'value' => $get['search'],
    'compare' => 'LIKE'
  )
);

Replace $get['search'] with your search value

0

Here's another way, just change the request with the 'posts_where_request' filter. Everything will still be the default except ('s' AND 'meta_query') => ('s' OR 'meta_query').

AND ( ((posts.post_title LIKE 'Lily') OR (posts.post_excerpt LIKE 'Lily') OR (posts.post_content LIKE 'Lily')) )
AND ( ( postmeta.meta_key = 'author' AND postmeta.meta_value LIKE 'Lily' ) )

=>

AND ( 
    ( ( postmeta.meta_key = 'author' AND postmeta.meta_value LIKE 'Lily' ) )
    OR
    ((posts.post_title LIKE 'Lily') OR (posts.post_excerpt LIKE 'Lily') OR (posts.post_content LIKE 'Lily'))
)

this is the code

function edit_request_wp_query( $where ) {
    global $wpdb;
    if ( strpos($where, $wpdb->postmeta.'.meta_key') && strpos($where, $wpdb->posts.'.post_title') ) {
        $string = $where;
        $index_meta = index_argument_in_request($string, $wpdb->postmeta.'.meta_key', $wpdb->postmeta.'.meta_value');
        $meta_query = substr($string, $index_meta['start'], $index_meta['end']-$index_meta['start']);
        $string = str_replace( $meta_query, '', $string );

        $meta_query = ltrim($meta_query, 'AND').' OR '; 
        $index_s = index_argument_in_request($string, $wpdb->posts.'.post_title');
        $insert_to = strpos($string, '(', $index_s['start'])+1;
        $string = substr_replace($string, $meta_query, $insert_to, 0);

        $where = $string;
    }
    return $where;
}
add_filter('posts_where_request', 'edit_request_wp_query');

function index_argument_in_request($string, $key_start, $key_end = '') {
    if (!$key_end) $key_end = $key_start;
    $index_key_start = strpos($string, $key_start);
    $string_before = substr($string, 0, $index_key_start);
    $index_start = strrpos($string_before, 'AND');

    $last_index_key = strrpos($string, $key_end);
    $index_end = strpos($string, 'AND', $last_index_key);

    return ['start' => $index_start, 'end' => $index_end];
}

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.