[Resolved] OR + AND in the same filter?

[Author]

Posts

[Ido Angel]

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.

[Christian Cox]

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.

[Ido Angel]

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!

[Christian Cox]

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?

[Ido Angel]

exactly!

[Christian Cox]

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.

[Ido Angel]

As always - thorough and creative. Thanks Christian!