2

How to add "OR" relation in "tax_query" and "meta_query". Because the default is "AND"

[query] => Array
        (
            [relation] => OR
            [posts_per_page] => 3
            [offset] => 0
            [orderby] => post_date
            [order] => DESC
            [post_type] => biography
            [post_status] => publish
            [meta_query] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [key] => f_name
                            [value] => gh
                            [compare] => LIKE
                        )
                    [1] => Array
                        (
                            [key] => l_name
                            [value] => gh
                            [compare] => LIKE
                        )
                )
            [tax_query] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [taxonomy] => historical-period
                            [field] => term_id
                            [terms] => Array
                                (
                                    [0] => 27
                                )
                            [operator] => IN
                        )
                )
        )

We got the below result

Result

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (27)) 
**AND **
(( wp_postmeta.meta_key = 'f_name' AND wp_postmeta.meta_value LIKE '%gh%' ) OR ( wp_postmeta.meta_key = '%gh%' )) AND wp_posts.post_type = 'biography' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 3

We need the below result

Result

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (27)) 
**OR**
(( wp_postmeta.meta_key = 'f_name' AND wp_postmeta.meta_value LIKE '%gh%' ) OR ( wp_postmeta.meta_key = '%gh%' )) AND wp_posts.post_type = 'biography' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 3

Please help to adding a "OR" relation between "tax_query" and "meta_query"

I have also tried the below code. But we have not gotten an accurate result.

  1. filter_query
add_filter('get_meta_sql', 'filter_query', 10, 1);
$posts = new WP_Query($post_args);
remove_filter('get_meta_sql', 'filter_query');

function filter_query($sql){
    $pos = strpos($sql['where'], 'AND');
    if ($pos !== false) {
        $sql['where'] = substr_replace($sql['where'], 'OR', $pos, strlen('AND'));
    }
    return $sql;
}

Reference link:

How to give OR relation in tax_query and meta_query Wordpress

Problem:

if we used the above code it added "OR" relations in the whole query including the meta_key and meta_value relation.

  1. Manually merge the queried posts
$photos_query = new WP_Query( $photos );
$quotes_query = new WP_Query( $quotes );
$result = new WP_Query();

// start putting the contents in the new object
$result->posts = array_merge( $photos_query->posts, $quotes_query->posts );

// here you might wanna apply some sort of sorting on $result->posts

// we also need to set post count correctly so as to enable the looping
$result->post_count = count( $result->posts );

Problem: If we used the above code we have found so many duplicate results. and it taking so much time. Sometime it not returning the result because query execution session time out.

Reference link:

https://wordpress.stackexchange.com/questions/71576/combining-queries-with-different-arguments-per-post-type/71582#71582

Hitesh Techarck
  • 21
  • 4
  • _"I have also tried the below code. But we have not gotten an accurate result."_ - did it not work in that it did not replace the AND with OR in the desired position of the statement? Or did it, but the result were still not what you expected? – CBroe Mar 15 '23 at 07:03
  • @CBroe *if we used the above code it added "OR" relations in the whole query including the meta_key and meta_value relation.* – Nigel Ren Mar 15 '23 at 07:17
  • I don't see how that would be possible, that code is explicitly written to replace the _first_ occurrence only. – CBroe Mar 15 '23 at 07:24

2 Answers2

0
$tax_query = array(
    'taxonomy' => 'historical-period',
    'field' => 'term_id',
    'terms' => array( 27 ),
    'operator' => 'IN'
);

$meta_query = array(
    'relation' => 'OR',
    array(
        'key' => 'f_name',
        'value' => 'gh',
        'compare' => 'LIKE'
    ),
    array(
        'key' => 'l_name',
        'value' => 'gh',
        'compare' => 'LIKE'
    )
);

$meta_subquery = new WP_Query( array(
    'post_type' => 'biography',
    'post_status' => 'publish',
    'meta_query' => $meta_query,
    'fields' => 'ids'
) );


$main_query_args = array(
    'posts_per_page' => 3,
    'offset' => 0,
    'orderby' => 'post_date',
    'order' => 'DESC',
    'post_type' => 'biography',
    'post_status' => 'publish',
    'tax_query' => $tax_query,
    'post__in' => $meta_subquery->posts
);

$main_query = new WP_Query( $main_query_args );

SQL:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE wp_posts.post_type = 'biography'
AND wp_posts.post_status = 'publish'
AND (
    wp_term_relationships.term_taxonomy_id IN (27)
    OR wp_posts.ID IN (
        SELECT post_id
        FROM wp_postmeta
        WHERE (
            (meta_key = 'f_name' AND meta_value LIKE '%gh%')
            OR (meta_key = 'l_name' AND meta_value LIKE '%gh%')
        )
    )
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 3;
4efirrr
  • 231
  • 1
  • 5
  • Did you change anything in `$args` to achieve this? – Nigel Ren Mar 15 '23 at 07:10
  • 1
    I think OP wants the AND on the 9th line to be an OR, so it matches either the tax or the meta query. – Nigel Ren Mar 15 '23 at 07:13
  • @NigelRen misunderstood the issue. I changed the code, solution - via two queries to get the desired subquery with OR – 4efirrr Mar 15 '23 at 07:35
  • @4efirrr I have already used the subquery code. but not getting an accurate result. I have used the above code but we found the below SQL code in "[request]" SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'biography' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.post_date DESC LIMIT 0, 3 Also, the above WP_Query code is not working properly. it returns the above SQL result. – Hitesh Techarck Mar 15 '23 at 10:55
  • @4efirrr We need a relation between tax_query and meta_query with "OR" – Hitesh Techarck Mar 15 '23 at 10:58
0

I have tried to solve multiple ways to add a "OR" relation between "tax_query" and "meta_query" But still, we not getting accurate results. So at the last, I used WordPress SQL query and got a result.

$all_sql="SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (27)) 
OR
(( wp_postmeta.meta_key = 'f_name' AND wp_postmeta.meta_value LIKE '%gh%' ) OR ( wp_postmeta.meta_key = '%gh%' )) AND wp_posts.post_type = 'biography' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 3"

$all_result = $wpdb->get_results($all_sql);

Thanks Nigel Ren, CBroe and, 4efirrr your support.

Hitesh Techarck
  • 21
  • 4