Skip Navigation

[Resolved] OR + AND in the same filter?

This thread is resolved. Here is a description of the problem and solution.

Problem: I have a custom search View that includes 4 custom taxonomy filters. I would like to set one of the filters to a static value, so that all the results include one term from that custom taxonomy. Then I would like to use the relation "AND" to combine that with a group of 3 other custom taxonomy filters, which can be chosen on the front-end by the User, and combined using the "OR" relation. The combined effect should be like this graphic representation:

------------------------------------------------------
| Recipe Category (equal to "Shake")                 |
|                                                    |
| - AND -                                            |
|                                                    |
|    ____________________________________________    |
|    |                                           |   |
|    | Main Ingredient (set by URL parameter)    |   |
|    |                                           |   |
|    |  - OR -                                   |   |
|    |                                           |   |
|    | Extra Ingredient (set by URL parameter)   |   |
|    |                                           |   |
|    |  - OR -                                   |   |
|    |                                           |   |
|    | Fluid (set by URL parameter)              |   |
|    --------------------------------------------    |
|                                                    |
-----------------------------------------------------

Solution: This type of combination of filters with nested AND/OR relations is not possible through the standard Views GUI, and requires a custom code solution. We offer the wpv_filter_query API that allows you to intercept the query and modify its tax_query details in real-time.
You can read more about complex or nested relations in taxonomy-based queries in the documentation linked below.
An example showing these APIs used together:

function tssupp_change_tax_query_and_or($view_args, $view_settings, $view_id)
{
  $view_ids = array( 22575 ); // comma-separated list of View IDs where you want to apply this filter
  $static_tax = 'recipe-category'; // the slug of the taxonomy that includes the static term "shake"
  $static_term_id = 1084; // the term ID of "shake" in the static taxonomy "recipe-category"
   
/*
-- do not edit below this line -- 
*/
 
  if ( in_array($view_id, $view_ids) ) {
    // new tax query template
    $new_tax_query = array(
      array(
        // code below will unshift selected front-end tax filters into this array
        'relation' => 'OR',
      ),
      array(
        'taxonomy' => $static_tax,
        'field' => 'id',
        'terms' => array( $static_term_id ),
        'operator' => 'IN',
        'include_children' => 1,
      ),
      'relation' => 'AND',
    );
    foreach ( $view_args['tax_query'] as $tax_query_term ) {
      // loop over selected filters,
      // verify is_array to skip 'relation' index,
      // verify the slug of this tax is not
      // same as the static taxonomy slug,
      // unshift it into the new query's OR block
      if( is_array($tax_query_term) && $tax_query_term['taxonomy'] != $static_tax ){
        array_unshift( $new_tax_query[0], $tax_query_term );
      }
    }
    // now overwrite the original tax_query with
    // the new tax_query and you're done
    $view_args['tax_query'] = $new_tax_query;
  }
 
  return $view_args;
}
add_filter('wpv_filter_query', 'tssupp_change_tax_query_and_or', 99, 3);

Up at the top, modify the View if necessary to customize it for your site. You may add other View IDs to the $view_ids array in comma-separated format if you have more than one View with similar filters where you want to apply this custom snippet. I copied the taxonomy slugs and term IDs from the debug information, so I'm pretty sure those are correct.

Add the code in your child theme's functions.php file, or in Toolset > Settings > Custom Code, and test it on the front-end of the site. Let me know the results and we can go from there.

Relevant Documentation:
https://developer.wordpress.org/reference/classes/wp_query/#taxonomy-parameters
https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

This support ticket is created 3 years, 6 months ago. There's a good chance that you are reading advice that it now obsolete.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

Sun Mon Tue Wed Thu Fri Sat
8:00 – 12:00 8:00 – 12:00 8:00 – 12:00 8:00 – 12:00 8:00 – 12:00 - -
13:00 – 17:00 13:00 – 17:00 13:00 – 17:00 13:00 – 17:00 13:00 – 17:00 - -

Supporter timezone: America/New_York (GMT-04:00)

This topic contains 6 replies, has 2 voices.

Last updated by Ido Angel 3 years, 6 months ago.

Assisted by: Christian Cox.

Author
Posts
#1774571

Hey,
I have a view with a search which is supposed to filter shake recipes.
The idea is to first choose whatever ingredients you have in your fridge and then show which recipes can be made out of these ingredients.
Since I want to show results of recipes which include SOME of the ingredients (as opposed to showing only recipes which include ALL of them), I created a view with filters using OR relationship between them (main ingredients, fluids, and extra ingredients).
But since I have recipes for not only shakes, I had to add a filter which will screen only recipes from the "shakes" category.
But I think this messes up the results. Logically, I am supposed to get recipes which have ANY ingredient/fluid/extra chosen, but sometimes I'm just getting 0 results, which doesn't make sense.
You can see here:

hidden link

the bottom buttons are taking you to the next filter.

#1774963
Screen Shot 2020-09-13 at 2.22.51 PM.png

Hello, for more complex or non-standard field relations with AND/OR, you may need to manipulate the query directly using the Views filter wpv_filter_query, and change the relation between different fields or filter clauses to be "OR" instead of "AND". Documentation for that API is here: https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

Some examples in other tickets:
https://toolset.com/forums/topic/creating-a-multi-condition-andor-custom-filter-in-a-view/
https://toolset.com/forums/topic/sort-by-2-custom-fields/

I can help with less complex AND/OR modifications, but more complex modifications, those that require direct SQL manipulation for example, are outside the scope of support we provide here. More information about queries with direct SQL manipulation here: https://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

The correct syntax for the less complex version depends on which filters are custom fields, which filters are taxonomies, and the complexity of the AND/OR combinations. If you built this View using the classic editor, please open all the Query Filters and take a screenshot( or screenshots) showing the full details of configurations of all the filters. Please include this screenshot in your next reply.

Also, it will be helpful to see more technical details of the query for this View from Toolset Views debug mode. Go to Toolset > Settings > Front-end Content, click the checkbox "Enable Views debug mode", then select the option for full debug mode. Next, on the front-end of the site, visit this URL where I have selected at least two options from each "page" of filter options: hidden link
You should see the debug popup appear when the page loads. If not, update your browser popup settings to allow popups from this domain and reload the page. When you see the popup, open the Query Args, MySQL query, and wpv_filter_query sections for this View ID (screenshot attached). Copy the contents of each section and paste them in your next reply so I can see the query configurations. After that, you can turn off Views debug mode in Toolset > Settings > Front-end Content.

I'll review your comments and give you some additional feedback.

#1775373

Hey dear Christian and thanks for the reply!
In this case, all filters are taxonomies. I'm using "recipe category" (קטגוריות מתכונים) to initially separate the shake recipes in this specific view from other non-shake recipes, and the other filters are for the users - main ingredients, fluids and extras.
Here's the screenshot:

hidden link

And here's the debug windows content:

Page info
Current page hidden link
Total memory used 8.1688MB
Render time 1.11
Total MySQL queries 217
CPU usage 0.45%

Elements info
- Recipe Calculator Two (Recipes ordered by post date, descending)

ID 22575
Name Recipe Calculator Two (Edit)
Kind of element View
Render time 0.0238
Memory used 0.0764MB
Summary Recipes ordered by post date, descending
Query args Hide
Basic query arguments
Array
(
[post_type] => Array
(
[0] => recipe
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
)
MySQL query Hide
SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) LEFT JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (500,1115)
AND
tt1.term_taxonomy_id IN (24,1113)
AND
tt2.term_taxonomy_id IN (1091,1101)
AND
tt3.term_taxonomy_id IN (1084)
) AND wp_posts.post_type = 'recipe' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
Items found 0
Query results Hide
WP_Query Object
(
[query] => Array
(
[post_type] => Array
(
[0] => recipe
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => -1
[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => -1
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => main-ingredient
[field] => id
[terms] => Array
(
[0] => 1115
[1] => 500
)

[operator] => IN
[include_children] => 1
)

[1] => Array
(
[taxonomy] => fluid
[field] => id
[terms] => Array
(
[0] => 24
[1] => 1113

Filters
Filter arguments before the query using wpv_filter_query Hide
wpv_filter_query
Array
(
[post_type] => Array
(
[0] => recipe
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => -1
[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => -1
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => main-ingredient
[field] => id
[terms] => Array
(
[0] => 1115
[1] => 500
)

[operator] => IN
[include_children] => 1
)

[1] => Array
(
[taxonomy] => fluid
[field] => id
[terms] => Array
(
[0] => 24
[1] => 1113
)

[operator] => IN
[include_children] => 1
)

[2] => Array
(
[taxonomy] => extra-ingredient
[field] => id
[terms] => Array
(
[0] => 1101
[1] => 1091
)

[operator] => IN
[include_children] => 1
)

[3] => Array
(
[taxonomy] => recipe-category
[field] => id
[terms] => Array
(
[0] => 1084
)

[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[post_status] => Array
(
[0] => publish
[1] => private
)

[orderby] => date
[order] => DESC
)
Filter the returned query using wpv_filter_query_post_process Hide
wpv_filter_query_post_process
WP_Query Object
(
[query] => Array
(
[post_type] => Array
(
[0] => recipe
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => -1
[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => -1
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => main-ingredient
[field] => id
[terms] => Array
(
[0] => 1115
[1] => 500
)

[operator] => IN
[include_children] => 1
)

[1] => Array
(
[taxonomy] => fluid
[field] => id
[terms] => Array
(
[0] => 24
[1] => 1113
)

[operator] => IN
[include_children] => 1
)

[2] => Array
(
[taxonomy] => extra-ingredient
[field] => id
[terms] => Array
(
[0] => 1101
[1] => 1091
)

[operator] => IN
[include_children] => 1
)

[3] => Array
(
[taxonomy] => recipe-category
[field] => id
[terms] => Array
(
[0] => 1084
)

[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[post_status] => Array
(
[0] => publish
[1] => private
)

[orderby] => date
[order] => DESC
)

[query_vars] => Array
(
[post_type] => Array
(
[0] => recipe
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => -1
[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => -1
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => main-ingredient
[field] => id
[terms] => Array
(
[0] => 1115
[1] => 500
)

[operator] => IN
[include_children] => 1
)

[1] => Array
(
[taxonomy] => fluid
[field] => id
[terms] => Array
(
[0] => 24
[1] => 1113
)

[operator] => IN
[include_children] => 1
)

[2] => Array
(
[taxonomy] => extra-ingredient
[field] => id
[terms] => Array
(
[0] => 1101
[1] => 1091
)

[operator] => IN
[include_children] => 1
)

[3] => Array
(
[taxonomy] => recipe-category
[field] => id
[terms] => Array
(
[0] => 1084
)

[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[post_status] => Array
(
[0] => publish
[1] => private
)

[orderby] => date
[order] => DESC
[error] =>
[m] =>
[p] => 0
[post_parent] =>
[subpost] =>
[subpost_id] =>
[attachment] =>
[attachment_id] => 0
[name] =>
[pagename] =>
[page_id] => 0
[second] =>
[minute] =>
[hour] =>
[day] => 0
[monthnum] => 0
[year] => 0
[w] => 0
[category_name] =>
[tag] =>
[cat] =>
[tag_id] =>
[author] =>
[author_name] =>
[feed] =>
[tb] =>
[meta_key] =>
[meta_value] =>
[preview] =>
[s] =>
[sentence] =>
[title] =>
[fields] =>
[menu_order] =>
[embed] =>
[category__in] => Array
(
)

[category__not_in] => Array
(
)

[category__and] => Array
(
)

[post__in] => Array
(
)

[post__not_in] => Array
(
)

[post_name__in] => Array
(
)

[tag__in] => Array
(
)

[tag__not_in] => Array
(
)

[tag__and] => Array
(
)

[tag_slug__in] => Array
(
)

[tag_slug__and] => Array
(
)

[post_parent__in] => Array
(
)

[post_parent__not_in] => Array
(
)

[author__in] => Array
(
)

[author__not_in] => Array
(
)

[cache_results] => 1
[update_post_term_cache] => 1
[lazy_load_term_meta] => 1
[update_post_meta_cache] => 1
[nopaging] => 1
[comments_per_page] => 50
[no_found_rows] =>
[taxonomy] => main-ingredient
[term_id] => 1115
)

[tax_query] => WP_Tax_Query Object
(
[queries] => Array
(
[0] => Array
(
[taxonomy] => main-ingredient
[terms] => Array
(
[0] => 1115
[1] => 500
)

[field] => id
[operator] => IN
[include_children] => 1
)

[1] => Array
(
[taxonomy] => fluid
[terms] => Array
(
[0] => 24
[1] => 1113
)

[field] => id
[operator] => IN
[include_children] => 1
)

[2] => Array
(
[taxonomy] => extra-ingredient
[terms] => Array
(
[0] => 1101
[1] => 1091
)

[field] => id
[operator] => IN
[include_children] => 1
)

[3] => Array
(
[taxonomy] => recipe-category
[terms] => Array
(
[0] => 1084
)

[field] => id
[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[relation] => AND
[table_aliases:protected] => Array
(
[0] => wp_term_relationships
[1] => tt1
[2] => tt2
[3] => tt3
)

[queried_terms] => Array
(
[main-ingredient] => Array
(
[terms] => Array
(
[0] => 1115
[1] => 500
)

[field] => id
)

[fluid] => Array
(
[terms] => Array
(
[0] => 24
[1] => 1113
)

[field] => id
)

[extra-ingredient] => Array
(
[terms] => Array
(
[0] => 1101
[1] => 1091
)

[field] => id
)

[recipe-category] => Array
(
[terms] => Array
(
[0] => 1084
)

[field] => id
)

)

[primary_table] => wp_posts
[primary_id_column] => ID
)

[meta_query] => WP_Meta_Query Object
(
[queries] => Array
(
)

[relation] =>
[meta_table] =>
[meta_id_column] =>
[primary_table] =>
[primary_id_column] =>
[table_aliases:protected] => Array
(
)

[clauses:protected] => Array
(
)

[has_or_relation:protected] =>
)

[date_query] =>
[request] => SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) LEFT JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (500,1115)
AND
tt1.term_taxonomy_id IN (24,1113)
AND
tt2.term_taxonomy_id IN (1091,1101)
AND
tt3.term_taxonomy_id IN (1084)
) AND wp_posts.post_type = 'recipe' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
[posts] => Array
(
)

[post_count] => 0
[current_post] => -1
[in_the_loop] =>
[comment_count] => 0
[current_comment] => -1
[found_posts] => 0
[max_num_pages] => 0
[max_num_comment_pages] => 0
[is_single] =>
[is_preview] =>
[is_page] =>
[is_archive] => 1
[is_date] =>
[is_year] =>
[is_month] =>
[is_day] =>
[is_time] =>
[is_author] =>
[is_category] =>
[is_tag] =>
[is_tax] => 1
[is_search] =>
[is_feed] =>
[is_comment_feed] =>
[is_trackback] =>
[is_home] =>
[is_privacy_policy] =>
[is_404] =>
[is_embed] =>
[is_paged] =>
[is_admin] =>
[is_attachment] =>
[is_singular] =>
[is_robots] =>
[is_favicon] =>
[is_posts_page] =>
[is_post_type_archive] =>
[query_vars_hash:WP_Query:private] => 974eefc6e17b8033f509a4f8b93dc8e8
[query_vars_changed:WP_Query:private] =>
[thumbnails_cached] =>
[stopwords:WP_Query:private] =>
[compat_fields:WP_Query:private] => Array
(
[0] => query_vars_hash
[1] => query_vars_changed
)

[compat_methods:WP_Query:private] => Array
(
[0] => init_query_flags
[1] => parse_tax_query
)

)

THANKS!

#1776227

Got it, thanks. You want to have one "AND" relation between the Recipe Category taxonomy and a group of 3 other taxonomies. Within the group of 3 other taxonomies, you want to use an "OR" relation. Something like this:

------------------------------------------------------
| Recipe Category (equal to "Shake")                 |
|                                                    |
| - AND -                                            |
|                                                    |
|    ____________________________________________    |
|    |                                           |   |
|    | Main Ingredient (set by URL parameter)    |   |
|    |                                           |   |
|    |  - OR -                                   |   |
|    |                                           |   |
|    | Extra Ingredient (set by URL parameter)   |   |
|    |                                           |   |
|    |  - OR -                                   |   |
|    |                                           |   |
|    | Fluid (set by URL parameter)              |   |
|    --------------------------------------------    |
|                                                    |
-----------------------------------------------------

Is this correct?

#1776231

exactly!

#1777947

Okay I have a code snippet example for you:

function tssupp_change_tax_query_and_or($view_args, $view_settings, $view_id)
{
  $view_ids = array( 22575 );
  $static_tax = 'recipe-category';
  $static_term_id = 1084;
  
/*
-- do not edit below this line -- 
*/

  if ( in_array($view_id, $view_ids) ) {
    // new tax query template
    $new_tax_query = array(
      array(
        // unshift selected front-end tax filters here
        'relation' => 'OR',
      ),
      array(
        'taxonomy' => $static_tax,
        'field' => 'id',
        'terms' => array( $static_term_id ),
        'operator' => 'IN',
        'include_children' => 1,
      ),
      'relation' => 'AND',
    );
    foreach ( $view_args['tax_query'] as $tax_query_term ) {
      // loop over selected filters,
      // verify is_array to skip 'relation' index,
      // verify the slug of this tax is not
      // same as the static taxonomy slug,
      // unshift it into the new query's OR block
      if( is_array($tax_query_term) && $tax_query_term['taxonomy'] != $static_tax ){
        array_unshift( $new_tax_query[0], $tax_query_term );
      }
    }
    // now overwrite the original tax_query with
    // the new tax_query and you're done
    $view_args['tax_query'] = $new_tax_query;
  }

  return $view_args;
}
add_filter('wpv_filter_query', 'tssupp_change_tax_query_and_or', 99, 3);

Up at the top, modify the View if necessary to customize it for your site. You may add other View IDs to the $view_ids array in comma-separated format if you have more than one View with similar filters where you want to apply this custom snippet. I copied the taxonomy slugs and term IDs from the debug information, so I'm pretty sure those are correct.

Add the code in your child theme's functions.php file, or in Toolset > Settings > Custom Code, and test it on the front-end of the site. Let me know the results and we can go from there.

#1777949

As always - thorough and creative. Thanks Christian!

This ticket is now closed. If you're a WPML client and need related help, please open a new support ticket.