[Resolved] Multiple filters to use an AND rather than OR function

[Author]

Posts

[szymonF]

I'm trying to create new filters for my search view and archives.
I created all the filters but some of them seem to work using an AND function while others use an OR. It seems that the Topic, Language and Tradition filters work using OR, while the Times, Medium, Difficulty and Use use AND.

I want all of them to use an AND function. How can I do that?

Page: hidden link (need to click on 'Filters' to see the filters)

Code:

[wpv-filter-start hide="false"]
[wpv-filter-controls]

<div class="search-div newsearch">
<div>
<label class="search-box">[wpv-filter-search-box]<span class="nope">Search Box</span></label>
[wpv-filter-submit name="Search" type="input" class="search-submit"]
</div>

<dl class="accordion search-accordion">
<dt align="middle">
Filters
</dt>

<dd>
<div class="row">
<div class="col-6">
<div class="form-group search-topics">
<p>Topics (hold ctrl / ⌘ to select more or unselect)
[wpv-control-post-taxonomy taxonomy="entry_topic" type="multi-select" output="legacy" url_param="wpv-entry_topic"]
</div>
<div class="form-group search-languages">
<p>Languages (hold ctrl / ⌘ to select more or unselect)
[wpv-control-post-taxonomy taxonomy="entry_language" type="multi-select" output="legacy" url_param="wpv-entry_language"]
</div>
</div>
<div class="col-6">
<div class="form-group search-traditions">
<p>Traditions (hold ctrl / ⌘ to select more or unselect)
[wpv-control-post-taxonomy taxonomy="entry_tradition" type="multi-select" output="legacy" url_param="wpv-entry_tradition"]
</div>
<div class="form-group search-time">
<p>Times</p>
[double-range-filter]
<div class="search-time-input">
[wpv-control-postmeta class="time-filter-input" type="textfield" field="wpcf-entry-year2" url_param="wpv-wpcf-entry-year2" placeholder="From"]
<span class="search-time-between">-</span>
[wpv-control-postmeta class="time-filter-input" type="textfield" field="wpcf-year" url_param="wpv-wpcf-year" placeholder="To"]
<span class="search-time-between">or</span>
[wpv-control-postmeta values="-3000%%COMMA%%-800,-799%%COMMA%%-1,1%%COMMA%%799,800%%COMMA%%1499,1500%%COMMA%%1799,1800%%COMMA%%1929,1930%%COMMA%%1999,2000%%COMMA%%2024" display_values="3000 BCE – 800 BCE,799 BCE – 1 BCE,1 CE – 799 CE,800 CE – 1499 CE,1500 CE – 1799 CE,1800 CE – 1929 CE,1930 CE – 1999 CE,2000 CE – 2024 CE" field="wpcf-time-period" type="select" source="custom" default_label="Time Period" url_param="wpv-wpcf-time-period"]
</div>
</div>
</div>
</div>
<div class="search-checkboxes">
<div class="search-medium">
[wpml-string context="wpv-views"]<p>Medium:</p>[/wpml-string]
[wpv-control field="medium" url_param="medium" type="checkboxes" values=",1,2,3,4" display_values="Any,Book,Chapter,Article,Encyclopaedia"]
</div>

<div class="search-use">
[wpml-string context="wpv-views"]<p>Recommended use:</p>[/wpml-string]
[wpv-control field="use" url_param="use" type="checkboxes" values=",introductory,overview,further,specialised" display_values="Any,Introductory,Overview,Further,Specialised"]
</div>

<div class="search-difficulty">
[wpml-string context="wpv-views"]<p>Difficulty:</p>[/wpml-string]
[wpv-control field="difficulty" url_param="difficulty" type="checkboxes" values=",easy,intermediate,advanced" display_values="Any,Easy,Intermediate,Advanced"]
</div>
</div>
</dd>
</dl>
</div>
<div class="article-end"> </div>
<br>

[/wpv-filter-controls]
[wpv-filter-end]

[Minesh]

Hello. Thank you for contacting the Toolset support.

When I checked, you have added the frontend filters for Topic, Language and Tradition as multiselect filters.

By default, when you have multiselect filters, it will apply the OR for multiselect options.

Do you want to apply AND for multiselect options as well as for the multiple fields?

So do you mean irrespective of whatever filters and options selected you want to apply AND clause?

In addition to that - Could you please send me debug information that will help us to investigate your issue.
=> https://toolset.com/faq/provide-debug-information-faster-support/

[szymonF]

Hi Minesh, thanks for looking into it. The debug info is here.

Yes, that's correct, I'd like to apply an 'AND' function between all different filters, but 'OR' within the filters. So structure should be:
(filter 1 option A OR filter 1 option B ...) AND (f2 oA OR f2 oB ...) AND (f3 oA OR f3 oB ...) etc.

For example, user should be able to find only entries which are easy OR intermediate to read AND are books AND on the Topics of aesthetics OR epistemology, AND in the analytic Tradition, AND published in English OR French, AND within a specified timeframe.

[Minesh]

To check what filter clause is currently apply, can you please send me admin access details.

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I have set the next reply to private which means only you and I have access to it.

[Minesh]

It seems to be working as expected.

When I checked the "Query Filter" section and I can see that all fields are having "AND" clause between them. Please check the following screenshot:
- hidden link

And when I try to filter the view results by selecting multiple filters I can see that it does apply "AND" condition between and "OR" clause within the multiple options of the same filter.

SELECT SQL_CALC_FOUND_ROWS  wpnc_posts.ID
					 FROM wpnc_posts  LEFT JOIN wpnc_term_relationships ON (wpnc_posts.ID = wpnc_term_relationships.object_id)  LEFT JOIN wpnc_term_relationships AS tt1 ON (wpnc_posts.ID = tt1.object_id) INNER JOIN wpnc_postmeta ON ( wpnc_posts.ID = wpnc_postmeta.post_id )  INNER JOIN wpnc_postmeta AS mt1 ON ( wpnc_posts.ID = mt1.post_id )  INNER JOIN wpnc_postmeta AS mt2 ON ( wpnc_posts.ID = mt2.post_id )  INNER JOIN wpnc_postmeta AS mt3 ON ( wpnc_posts.ID = mt3.post_id )
					 WHERE 1=1  AND ( 
  wpnc_term_relationships.term_taxonomy_id IN (5391,7653,8124) 
  AND 
  tt1.term_taxonomy_id IN (4778,4779)
) AND ( 
  wpnc_postmeta.meta_key = 'wpcf-author' 
  AND 
  ( 
    ( mt1.meta_key = 'wpcf-medium' AND mt1.meta_value IN ('1') ) 
    AND 
    ( mt2.meta_key = 'wpcf-use' AND mt2.meta_value IN ('introductory') ) 
    AND 
    ( mt3.meta_key = 'wpcf-entry-year2' AND mt3.meta_value = '-3000' )
  )
) AND wpnc_posts.post_type = 'post' AND ((wpnc_posts.post_status = 'publish' OR wpnc_posts.post_status = 'private'))
					 GROUP BY wpnc_posts.ID
					 ORDER BY wpnc_postmeta.meta_value ASC
					 LIMIT 0, 10

[szymonF]

Hi Minesh, thanks for looking into this.
I'm not sure why then, but I just don't get the desired effects. Have a look at the attached screenshot - I selected a topic, tradition and language, but only the language is right on the filtered entry, the others are missing.
In general, it doesn't even seem like the filter is using an 'OR' function - instead, it seems to completely ignore all except the last filter. So if I only filter by Topic, it finds the right topics. If I filter by Topic and Tradition, it ignores the topics and finds entries which match the Tradition. If by Topic, Tradition and Language, it ignores topics and traditions and finds matching language entries.
What's happening?

You can test this here: hidden link

[Minesh]

Well - when I checked for the same field selection I see the following query is generated by view:

SELECT SQL_CALC_FOUND_ROWS wpnc_posts.ID
FROM wpnc_posts LEFT JOIN wpnc_term_relationships ON (wpnc_posts.ID = wpnc_term_relationships.object_id) LEFT JOIN wpnc_term_relationships AS tt1 ON (wpnc_posts.ID = tt1.object_id) LEFT JOIN wpnc_term_relationships AS tt2 ON (wpnc_posts.ID = tt2.object_id) INNER JOIN wpnc_postmeta ON ( wpnc_posts.ID = wpnc_postmeta.post_id ) INNER JOIN wpnc_postmeta AS mt1 ON ( wpnc_posts.ID = mt1.post_id )
WHERE 1=1 AND (
wpnc_term_relationships.term_taxonomy_id IN (5391)
AND
tt1.term_taxonomy_id IN (4895)
AND
tt2.term_taxonomy_id IN (4784)
) AND (
wpnc_postmeta.meta_key = 'wpcf-author'
AND
(
( mt1.meta_key = 'wpcf-entry-year2' AND mt1.meta_value = '-3000' )
)
) AND wpnc_posts.post_type = 'post' AND ((wpnc_posts.post_status = 'publish' OR wpnc_posts.post_status = 'private'))
GROUP BY wpnc_posts.ID
ORDER BY wpnc_postmeta.meta_value ASC
LIMIT 0, 10

I'm not sure from where entry-year2 is coming from. Can you please check all the query filters and check if its correctly configured with its type like "equal to" or "between" etc..etc..

Please tell me with what filter you have issue with. Please check all filters one by one.

[szymonF]

I'm sorry, I don't understand what all of this means. I just see that the result is not what I expected. The filters I have issue with are Topics, Traditions and Languages. I described the problem above.

entry-year2 - this was a field I thought I would be using but instead I ended up making the year field to allow multiple instances. year2 shouldn't be there.

[Minesh]

Well - as you can notice with my previous reply:
- https://toolset.com/forums/topic/multiple-filters-to-use-an-and-rather-than-or-function/#post-2786230

The taxonomy is having "AND" relation.

If you can share specific requirement with exact test case I'm happy to investigate this further. Please share what value I should select for all filters and what is your expected results.

[szymonF]

Thanks for clarifying. I understand it seems fine on the back end, but it doesn't work. Naturally, my users won't be satisfied to know that the taxonomy has the right relation in the code you pasted, if on the front end things just don't work.

I have a specific requirement: that the filters return front end results which satisfy all of the selected criteria. I shared a specific test case on the picture above - selecting Topic: Metaphysics of Aesthetics, Tradition: North American and European, and Language: Ancient Greek, should return only entries which are classified under: Metaphysics of Aesthetics AND (North American OR European) AND Ancient Greek. Instead, it just returns all entries classified under Ancient Greek.

Here is a direct link that showcases the problem: hidden link

Please help me fix this or perhaps pass this on to somebody who can help. Thanks!

[Minesh]

There are couple of things needs to sorted out. As you are using relevanssi when you use text search the whole control of the search will be passed to relevanssi and relevenssi will return the results.

Now, regarding the filter you added about -3000 to 2024 to what custom field you want to check against those values? What will be be default value when you search for other filters?

Like as you shared the URL:
=> hidden link

What will be the default value for the -3000 to 2024 filter and to what custom field you want to check against those values - if user enter anything otherthan -3000 to 2024.

Currently as you may noticed, it passes the -3000 value by default to the sql query:

( mt1.meta_key = 'wpcf-entry-year2' AND mt1.meta_value = '-3000' )

Do you want to check -3000 to 2024 values means posts between -3000 to 2024 against custom field "Publication Year"?

[szymonF]

Thanks for looking into it in more detail. I'm happy to remove Relevanssi if it's causing problems.

The time filter should only use the wpcf-year field (Publication year). If the user does not change anything in this filter, the default values should be as is: -3000 - 2024 (full range). This is a multiple instance field, so the filter should find an entry if at least one of the dates entered in it is within the search query.

As to the wpcf-entry-year2, maybe it's best to explain what this is meant to do. The database gathers philosophy texts. Every text was published on a certain date, hence the wpcf-year field. On top of that, a text X might be a reply to a text Y. The idea was that the search function will find such a reply text both when the user queries a date range that includes X's publication date, as well as Y's date. My initial idea was to create a separate custom field for all the Y dates (wpcf-entry-year2), but then I realised it's better to just have all dates in the same multiple instance field (wpcf-year). So wpcf-entry-year2 won't actually be used anymore. Does this make sense?

But is the time filter in need of attention? I thought it works just fine and it does seem to operate with an AND function. It's more about the Topic / Tradition / Language filters.

[Minesh]

Can we start with basic filters.
- hidden link

I see following filter code added to your view's Search and Pagination section:

[wpv-control-postmeta class="time-filter-input" type="textfield" field="wpcf-entry-year2"  url_param="wpv-wpcf-entry-year2" placeholder="From"] 
               <span class="search-time-between">-</span> 
               [wpv-control-postmeta class="time-filter-input" type="textfield" field="wpcf-year" url_param="wpv-wpcf-year" placeholder="To"]

If you see the filter section: hidden link
- As you can see one is using filed "wpcf-year" that displays the input textbox filter textbox with value 2024.
- The other field "wpcf-entry-year2" is displays the input textbox filter with value -3000

Further I see you added following filter:

[wpv-control-postmeta values="-3000%%COMMA%%-800,-799%%COMMA%%-1,1%%COMMA%%799,800%%COMMA%%1499,1500%%COMMA%%1799,1800%%COMMA%%1929,1930%%COMMA%%1999,2000%%COMMA%%2024" display_values="3000 BCE – 800 BCE,799 BCE – 1 BCE,1 CE – 799 CE,800 CE – 1499 CE,1500 CE – 1799 CE,1800 CE – 1929 CE,1930 CE – 1999 CE,2000 CE – 2024 CE" field="wpcf-time-period" type="select" source="custom" default_label="Time Period" url_param="wpv-wpcf-time-period"]

But there is no such feild I can see with the Entry post type namely "time-period".

Basically all those above filters you want to check against the custom field "Publication Year" (wpcf-year)? Is that correct? if yes:

I would like to know from where it sets the default value -3000 and 2024?

[szymonF]

I paid a developer to create this filter as I didn't know how to do it or how to build the slider with which users can change the input dates. So I don't exactly know the answers to those questions. But now I see that they've made a mess of it - although somehow it works. The 'time period' thing is a dropdown selector allowing users to autofill the filter with a historical period (e.g. Antiquity, Middle Ages, Renaissance, etc.) just to make searching more intuitive for the users.

The default values are set as such because of the site content - the oldest texts we have catalogued were published around 3000 BCE, while the newest would be the current year. I don't know where the values come from on a technical side. The developer said she created a PHP file in my theme directory which makes the time filter work - should I copy it in here?

But yes, all parts of the time filter should check only against the multiple instance wpcf-year field, and find a text if at least one of the dates entered in that field is within the filter range. As far as I understood, the slider and the time-period dropdown are not separate filters - all they do is feed values to the From and To field of the actual filter. Is that right?

[Minesh]

You will have to get in touch with the Developer and check with them as that is custom code and beyond the scope of our support policy.

With native filters - there should not be any issues. You may try to customize your search form to use native filters or check with the Developer you hire.