Multiple relationship for multiple tax_query in WP_Query WordPress

house.tc comments:

Wow, there are few parts that I don't understand.

I am using tax_query in per_get_posts, so how do I remove the filter?
add_filter('posts_where','custom_and_or');

$query=new WP_Query($args);

remove_filter('posts_where','custom_and_or');


In my query the relation should be AND.

So my custom_and_or should be like this? (according to my question above )

function custom_and_or($where){

// change some OR/AND to our needs

$where = str_replace("AND tt2.term_taxonomy_id","OR tt2.term_taxonomy_id",$where);

$where = str_replace("AND tt3.term_taxonomy_id","OR tt3.term_taxonomy_id",$where);

return $where;

}

Actually the relation I want is
(array_1 AND array_3) AND (array_4 OR array_2)

Does my filter above illustrate this?

I am not quite sure how the tt2 or tt3 (before term_taxonomy_id) come from.

Thanks

Dbranes comments:

You could just try this in a template (just to play with it)


wp_reset_query();
wp_reset_postdata();
add_filter('posts_where','custom_and_or');
$query = new WP_Query($args);
remove_filter('posts_where','custom_and_or');

printf("<div><strong>DEBUG:</strong><pre style='word-wrap: break-word;margin:10px;padding:5px;background-color:#ddd;'>%s</pre></div>", $query->request);
print "<ul>";
while ( $query->have_posts() ) : $query->the_post();
printf("<li>%s</li>",get_the_title());
endwhile;
print "</ul>";
wp_reset_query();
wp_reset_postdata();


where <strong>$args</strong> has four arrays in the <strong>tax_query</strong> with <strong>OR</strong> relation.

Then this code show you the <em>SQL</em> query. I used it on my install and with the <strong>custom_and_or filter</strong> commented out, I got:

SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
INNER JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id)
INNER JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (60,61) OR tt1.term_taxonomy_id IN (57,63,74,88) OR tt2.term_taxonomy_id IN (73,94) OR tt3.term_taxonomy_id IN (1,38) )
AND wp_posts.post_type IN ('post', 'page', 'attachment')
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_date DESC


So the part that we are interested in is

AND ( wp_term_relationships.term_taxonomy_id IN (60,61)
OR tt1.term_taxonomy_id IN (57,63,74,88)
OR tt2.term_taxonomy_id IN (73,94)
OR tt3.term_taxonomy_id IN (1,38)
)


And to change it into this:

AND ( ( tfl_term_relationships.term_taxonomy_id IN (60,61)
AND tt1.term_taxonomy_id IN (57,63,74,88) )
AND ( tt2.term_taxonomy_id IN (73,94)
OR tt3.term_taxonomy_id IN (1,38) )
)


(actually we don't need all these parentheses, but let's have add them all) we can use:

function custom_and_or($where){
global $wpdb;
// change some OR/AND to our needs
$where = str_replace("wp_term_relationships.term_taxonomy_id", "(wp_term_relationships.term_taxonomy_id",$where);
$where = str_replace("OR tt1.term_taxonomy_id","AND tt1.term_taxonomy_id",$where);
$where = str_replace(" OR tt2.term_taxonomy_id",") OR ( tt2.term_taxonomy_id",$where);
$where = str_replace("OR tt3.term_taxonomy_id","OR tt3.term_taxonomy_id",$where);
$where = str_replace("AND wp_posts.post_type",") AND wp_posts.post_type",$where);
return $where;
}


house.tc comments:

Okay, I get how the it works.
Now the question is how do I use it in pre_get_posts with post_where filter?

Dbranes comments:

You could try to target the main query using:

add_filter('posts_where','custom_and_or_main',10,2);
function custom_and_or_main($where, $query){
if(!is_admin() && $query->is_main_query()){
//
// change where here
//
}
return $where;
}


in the plugin code or functions.php.

house.tc comments:

So you mean we no need to remove the filter after using it? I am using this in search.

Dbranes comments:

Try this to target the search main query:

add_filter('posts_where','custom_and_or_main',10,2);
function custom_and_or_main($where, $query){
if(!is_admin() && $query->is_main_query() && $query->is_search() ){
//
// change where here
//
}
return $where;
}


Dbranes comments:

ps: I don't think you need to remove this filter, since we are targeting the main query.

One thing to notice, is that if the user is using another custom posts_where filter, he might overwrite our stuff,
but he can of course overwrite anything in general ;-)

We might also set some high (or low?) priority to your posts_where filter.

house.tc comments:

But i still have other parameters have to set in per_get_posts such as post_type, meta_query etc. So I need to change the WHERE for those parameter in this function as well? If so, there will be lot more things need to add to this function, which is not a ideal way to do it.

Dbranes comments:

do you mean: how to add post_type, meta_query, ... etc
into pre_get_posts
in general for the search main query?

house.tc comments:

Yes, that is what I meant.
However, I have another idea:
Can I set the priority of post_where filter after pre_get_posts so that the changes I made is on the tax_query only?

Dbranes comments:

ok, we are going far away from your original question, which didn't mention search, main query and pre_get_posts ;-)

The <strong>posts_where</strong> filter should run after the <strong>pre_get_posts</strong> filter in general.

One could try this to customize the main search query with some extra spice:

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

$query->set( 'post_type', 'post' );
$query->set( 'post_status', 'publish' );
$query->set( 'orderby', 'post_date' );
$query->set( 'order', 'DESC' );
$query->set( 'post_per_page', -1 );

$tax_query = array(
'relation' => 'OR',
array(
'taxonomy' => 'taxonomy1',
'field' => 'slug',
'terms' => array( $term)
),
array(
'taxonomy' => 'taxonomy3',
'field' => 'slug',
'terms' => array( $term3),
'operator' => 'IN',
),
array(
'taxonomy' => 'taxonomy4',
'field' => 'slug',
'terms' => array( $term4)
),
array(
'taxonomy' => 'taxonomy2',
'field' => 'slug',
'terms' => array( $term2),
'operator' => 'IN',
)
);

$query->set( 'tax_query', $tax_query );
}
return $query;
}
add_filter( 'pre_get_posts', 'custom_search_query' );


and then use the posts_where filter as before to change AND/OR.

house.tc comments:

Okay, got it. I have tried it and it works. Thanks.

Now I thinking a way to make the post_where working dynamically. I will to figure it out.

Thanks again, you have been very helpful. : )

Dbranes comments:

ok great, good luck with your project.