10

How do I query by meta_value or title?

If I set meta_values,

$args['meta_query'] = array(
   'relation' => 'OR',
    array(
        'key' => 'model_name',
        'value' => $thesearch,
        'compare' => 'like'
    )
);

they automatically get added as AND.

This search would be:

 WHERE title = 'Search' AND (model_name = 'Search' OR ...)

I need

WHERE title = 'Search' OR (model_name = 'Search' OR ...)
1
  • I tried adding $args['relation'] = 'OR'; But, it is not recognized. Seems like I missing how to control conditional logic through args.
    – Bryan
    Feb 18 '15 at 1:04
23

Note that the relation part in the meta_query argument, is only used to define the relation between the sub meta queries.

You can try this setup:

$args = [ 
    '_meta_or_title' => $thesearch,   // Our new custom argument!
    'meta_query'    => [
        [
            'key'     => 'model_name',
            'value'   => $thesearch,
            'compare' => 'like'
        ]
    ],
];

where we have introduced a custom argument _meta_or_title to activate the meta OR title query.

This is supported by the following plugin:

<?php
/**
 *  Plugin Name:   Meta OR Title query in WP_Query
 *  Description:   Activated through the '_meta_or_title' argument of WP_Query 
 *  Plugin URI:    http://wordpress.stackexchange.com/a/178492/26350
 *  Plugin Author: Birgir Erlendsson (birgire)
 *  Version:       0.0.1
 */

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;

            // Modify WHERE part:
            $sql['where'] = sprintf(
                " AND ( %s OR %s ) ",
                $wpdb->prepare( "{$wpdb->posts}.post_title = '%s'", $title ),
                mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
            );
            return $sql;
        });
    }
});
16
  • Wow. Awesome answer! This should be in the core.
    – Bryan
    Feb 18 '15 at 20:06
  • 1
    The answer got me 1/2 way there. wordpress.stackexchange.com/questions/178574/… Any idea on this next one?
    – Bryan
    Feb 18 '15 at 20:16
  • I have used this answer to create a query for one specific meta value on the edit.php page for a custom post type. I don't query the title at all, because my custom post type has no title. Therefore, I replaced " AND ( %s OR %s ) " by " OR ( %s ) " in the sql query and got rid of the line $wpdb->prepare( "{$wpdb->posts}.post_title = '%s'", $title ), altogether. Is this a valid approach?
    – BdN3504
    May 17 '15 at 10:30
  • 6
    Great trick. If you want a substring search for the title, replace post_title = '%s' with post_title like '%%%s%%'.
    – jarnoan
    Sep 30 '15 at 10:11
  • 1
    @jeff I once wrote a plugin to combine queries: github.com/birgire/wp-combine-queries. Also here I played with search (title, excerpt, content ) or tax query. Here I found another similar answer that I experimented with. We can also write two separate WP_Query, then collect and combine the resulting post ids into a third WP_Query (if needed and if PHP ordering is not enough) via post__in input parameter.
    – birgire
    Oct 13 '18 at 17:39
1

I'm quite new in WP, I haven't tested too much this approach I've came up with. Maybe you can help me to check if i am correct. The solution I've found so far is to implement the same meta_query logic, just making some replacements.

First, the usage:

$args = array(
    'lang' => 'pt', //this function does not conflict (example: polylang)
    'post_type' => 'produtos', 
    'post_status'   => 'publish',
    'posts_per_page' => 10,
    'paged' =>  1,
    'fields' => 'ids',
);
$args['meta_query] = [
    ['relation'] => 'OR'; //any relation you desire
    [
        'key' => 'acf_field', //any custom field (regular usage)
        'value' => $search, //any value (regular usage)
        'compare' => 'LIKE', //any comparition (regular usage)
    ],
    [
        'key' => 'post_title', //set the default post content of wordpress you desire ('post_content', 'post_title' and 'post_excerpt')
        'value' => $search, //any value
        'compare' => 'LIKE', //tested with 'LIKE and '=', works great and I can't realize other needs.
    ],
    [
        'key' => 'post_exerpt', // you can add how many times you need
        'value' => $search_2,
        'compare' => 'LIKE', 
    ],
];
$the_query = new WP_Query( $args ); //jus query
wp_reset_postdata(); //clean your query

To work, add this function to your theme functions.php

function post_content_to_meta_queries($where, $wp_query){
    global $wpdb;

    //if there is no metaquery, bye!
    $meta_queries = $wp_query->get( 'meta_query' );
    if( !$meta_queries || $meta_queries == '' ) return $where;

    //if only one relation
    $where = str_replace($wpdb->postmeta . ".meta_key = 'post_title' AND " . $wpdb->postmeta . ".meta_value", $wpdb->posts . ".post_title", $where);
    $where = str_replace($wpdb->postmeta . ".meta_key = 'post_content' AND " . $wpdb->postmeta . ".meta_value", $wpdb->posts . ".post_content", $where);
    $where = str_replace($wpdb->postmeta . ".meta_key = 'post_excerpt' AND " . $wpdb->postmeta . ".meta_value", $wpdb->posts . ".post_excerpt", $where);

    ////for nested relations

    //count the numbers of meta queries for possible replacements
    $number_of_relations = count($meta_queries);

    //replace 'WHERE' using the multidimensional postmeta naming logic used by wordpress core
    $i = 1;
    while($i<=$number_of_relations && $number_of_relations > 0){
        $where = str_replace("mt".$i.".meta_key = 'post_title' AND mt".$i.".meta_value", $wpdb->posts . ".post_title", $where);
        $where = str_replace("mt".$i.".meta_key = 'post_content' AND mt".$i.".meta_value", $wpdb->posts . ".post_content", $where);
        $where = str_replace("mt".$i.".meta_key = 'post_excerpt' AND mt".$i.".meta_value", $wpdb->posts . ".post_excerpt", $where);
        $i++;
    }

    return $where;
}

add_filter('posts_where','post_content_to_meta_queries',10,2);

I'm pretty sure it can be improved! Hope it helps!

0

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 is to add the following code in function.php, and whenever you us the 's' argument in WP_Query() and want it to search in one or several as well you simply add a 's_meta_keys' argument that is an array of the meta(s) key 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!

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.