Use Or relation for meta_query in wordpress

I try create search in wordpress not only by titles and content but also by meta fields.

So I wrote in functions.php:

Read More
function custom_search_query( $query ) {
    if ( !is_admin() && $query->is_search ) {

        $query->set('meta_query', array(
            'relation' => 'OR',
            array(
                'key' => 'country_name_work',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            )
        ));
       $query_vars = $query->query_vars;
       $query_vars['relation'] = 'OR';
    };

}
add_filter( 'pre_get_posts', 'custom_search_query');

The result SQL by search field Poland is:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (2,5,12,14,20) AND (((wp_posts.post_title LIKE '%poland%') OR (wp_posts.post_content LIKE '%poland%')))  **AND** ( 
  ( wp_postmeta.meta_key = 'country_name_work' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'jobs') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%poland%' DESC, wp_posts.post_date DESC LIMIT 0, 40

It would work fine if the query would be:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (2,5,12,14,20) AND (((wp_posts.post_title LIKE '%poland%') OR (wp_posts.post_content LIKE '%poland%')))  **OR** ( 
  ( wp_postmeta.meta_key = 'country_name_work' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' )
) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'jobs') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%poland%' DESC, wp_posts.post_date DESC LIMIT 0, 40

Operator AND should be OR.
I tried these suggestions –
meta_query, how to search using both relation OR & AND?
and
https://wordpress.stackexchange.com/questions/104060/using-or-conditions-in-meta-query-for-query-posts-argument – but it is not working correctly.

Updated:

Now I introduce answer of doublesharp. But I have issue again.
Now my code look like:

function post_title_filter( $where, &$query ){
    global $wpdb;
    if ( $post_title = $query->get( 'post_title' ) ){
        $post_title = $wpdb->esc_like( $post_title );
        $post_title = " '%{$post_title}%'";
        $title_filter_relation = strtoupper( $query->get( 'post_title_relation' ) ) == 'OR' ? 'OR' : 'AND';
        $where .= " {$title_filter_relation} {$wpdb->posts}.post_title LIKE {$post_title}";
    }
    return $where;
}

function post_content_filter( $where, &$query ){
    global $wpdb;
    if ( $post_content = $query->get( 'post_content' ) ){
        $post_content = $wpdb->esc_like( $post_content );
        $post_content = " '%{$post_content}%'";
        $content_filter_relation = strtoupper( $query->get( 'post_content_relation' ) ) == 'OR' ? 'OR' : 'AND';
        $where .= " {$content_filter_relation} {$wpdb->posts}.post_content LIKE {$post_content}";
    }
    return $where;
}
add_filter( 'posts_where', 'post_title_filter', 10, 2 );
add_filter( 'posts_where', 'post_content_filter', 10, 2 );

function custom_search_query( $query ) {
    if ( !is_admin() && $query->is_search) {
        //$query->is_search=false;
        $query->is_search1=true;
        $search = $query->query_vars['s'];
        $query->set( 'post_title', $search );
        $query->set( 'post_title_relation', 'OR' );
        $query->set( 'post_content', $search );
        $query->set( 'post_content_relation', 'OR' );
        $query->set( 'meta_query', array(
            'relation'=>'OR',
            array(
                'key' => 'country_name_work',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'sex',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'status',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'age-from',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'age-to',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'exp',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'work-env',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'work-conditions',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'schedule',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            ),array(
                'key' => 'salary',
                'value' => $query->query_vars['s'],
                'compare' => 'LIKE'
            )
        ));
    }
}
add_filter( 'pre_get_posts', 'custom_search_query' );

And generated query looks:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND (((wp_posts.post_title LIKE '%poland%') OR (wp_posts.post_content LIKE '%poland%'))) AND (wp_posts.post_password = '') AND ( ( wp_postmeta.meta_key = 'country_name_work' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'sex' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'status' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'age-from' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'age-to' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'exp' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'work-env' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'work-conditions' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'schedule' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) OR ( wp_postmeta.meta_key = 'salary' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%poland%' ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'jobs') AND (wp_posts.post_status = 'publish') OR wp_posts.post_title LIKE '%poland%' OR wp_posts.post_content LIKE '%poland%' GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%poland%' DESC, wp_posts.post_date DESC LIMIT 0, 40

Related posts

1 comment

  1. You will need to use the posts_where filter to add to the WHERE statement that is generated by WP_Query. This example adds two additional arguments – post_title and post_title_relation to allow you to where the title is LIKE a value OR there is a meta_key with that value.

    Use the posts_where and pre_get_posts filters

    function post_title_filter( $where, &$query ){
        global $wpdb;
        if ( $post_title = $query->get( 'post_title' ) ){
            $post_title = $wpdb->esc_like( $post_title );
            $post_title = " '%{$post_title}%'";
            $title_filter_relation = strtoupper( $query->get( 'post_title_relation' ) ) == 'OR' ? 'OR' : 'AND';
            $where .= " {$title_filter_relation} {$wpdb->posts}.post_title LIKE {$post_title}";
        }
        return $where;
    }
    add_filter( 'posts_where', 'post_title_filter', 10, 2 );
    
    function custom_search_query( $query ) {
        if ( !is_admin() && $query->is_search ) {
            $search = $query->query_vars['s'];
            $query->set( 'post_title', $search );
            $query->set( 'post_title_relation', 'OR' );
            $query->set( 'meta_query', array(
                array(
                    'key' => 'country_name_work',
                    'value' => $query->query_vars['s'],
                    'compare' => 'LIKE'
                )
            ));
        }
    }
    add_filter( 'pre_get_posts', 'custom_search_query' );
    

    Example using WP_Query directly

    $search = 'test';
    $args = array(
        'post_title' => $search,
        'post_title_relation' => 'OR',
        'meta_query' => array(
            array(
                'key' => 'test',
                'value' => $search,
                'compare' => 'LIKE'
            )
        )
    );
    $query = new WP_Query( $args );
    

Comments are closed.