Support archive | go to Low

Possible to specify AND/OR custom field searches?

Stephen 1 Mar 2013 06:54 idea, pending

Hi Low,

Is it possible to specify AND/OR searching when searching custom fields?

At the moment, if we have checkbox fields looking for custom fields that are 'not IS_EMPTY' and we tick say, three of them, the results will show just the records that match all three (what I call an 'AND' search - not sure if I'm getting the terminology correct).

But the client is asking if they can choose the behaviour for this - ideally, they'd like a dropdown menu that allows them to choose AND or OR. So if they chose OR in the above example, they'd get back results that have values in the 1st, 2nd, or 3rd checkbox.

I checked the docs and noticed the loose_ends and search_mode form fields, but they don't seem to apply to custom field searching (from the tests I just did).

Thanks,

Ste

Replies

Low 1 Mar 2013 09:02

Yeah, the loose_ends and search_mode parameters apply to the keywords field only.

If you're targeting 3 different fields, then it's not possible to switch between AND and OR at the moment. Having this:

<input name="search:field1" value="one"> 
 <input name="search:field2" value="two"> 
 <input name="search:field3" value="three">

...translates to this:

search:field1="one" search:field2="two" search:field3="three"

...which is native functionality and is always combined with AND.

Maybe you could consolidate the three checkboxes in a single checkbox group field. Then you could use the require_all parameter to switch between AND and OR:

<input name="search:field[]" value="one"> 
 <input name="search:field[]" value="two"> 
 <input name="search:field[]" value="three"> 
 <input name="require_all" value="search:field">

...where the value of require_all is empty for OR and the name of the targeted field if AND.

Stephen 14 Jun 2013 11:23

Hi Low,

I'm now just coming back to this comment about combining fields into a single checkbox group.

My fields still need to be separate, but I'm thinking that I can can create a new dummy custom field that contains a checkbox group, and have a script that updates this dummy custom field, which we'll use just for the searching.

So if I had a dummy field that looked like what's below, am I right in thinking that this checkbox group would work in the 'OR' fashion that I'm desiring? There are other fields in the search form that would work in the usual fashion.

Just making sure I understand you correctly so I can make sure I build the right thing for testing.

 
 <label for="testfield"><input type="checkbox" name="testfield[]" value="thursday" id="testfield"  />&nbsp;Thursday</label> 

 <label for="testfield"><input type="checkbox" name="testfield[]" value="friday" id="testfield"  />&nbsp;Friday</label> 

 <label for="testfield"><input type="checkbox" name="testfield[]" value="saturday" id="testfield"  />&nbsp;Saturday</label> 

 <label for="testfield"><input type="checkbox" name="testfield[]" value="sunday" id="testfield"  />&nbsp;Sunday</label> 

 <input name="require_all" value="search:testfield">

Thanks,

Stephen

Low 14 Jun 2013 11:26

Yes, as long as the names of the checkboxes are "search:testfield[]" instead of "testfield[]".

Low 14 Jun 2013 11:29

Oh, and you can use something like this as well:

<input type="checkbox" name="require_all" value="search:testfield"> 
 Match all selected

Stephen 14 Jun 2013 11:29

Okay thanks Low, I'll test this out.

This doesn't remove the feature request btw (as I've got loads of internal forms that I'd still love AND/OR searching on), but this might just give me a nice workaround with Low Search for a form on the public site that's quite popular, without having to go back to the old custom PHP way we used to use (ugly), before Low Search.

Thanks,

Stephen

Stephen 14 Jun 2013 11:36

Thanks for that extra comment - might be useful for users having the choice to match all days or any days. :-)

Stephen 14 Jun 2013 16:21

Hi Low,

I've just put together a quick test form but I can't seem to get this to work, so wondering if I misunderstood you on this, or whether you can see that I'm doing something wrong?

Every time I run this search (with any combination of days being checked), I keep getting 6 results (which is the number of results that match the hidden status). For the 6 entries in the results, I've got 4 entries with a different day of the week checked, 1 entry with nothing checked, and 1 entry with two days of the week checked.

Here's my code:

 
 {exp:low_search:form collection="ls_event_search" search_mode="all" result_page="/searches/test-search-results" secure="no" query="{segment_3}"} 

   <label for="testfield"><input type="checkbox" name="testfield[]" value="thursday" id="testfield"  />&nbsp;Thursday</label> 
   <label for="testfield"><input type="checkbox" name="testfield[]" value="friday" id="testfield"  />&nbsp;Friday</label> 
   <label for="testfield"><input type="checkbox" name="testfield[]" value="saturday" id="testfield"  />&nbsp;Saturday</label> 
   <label for="testfield"><input type="checkbox" name="testfield[]" value="sunday" id="testfield"  />&nbsp;Sunday</label> 

   <input type="hidden" name="require_all" value="search:testfield"> 
   <input type="hidden" name="status" value="Cancelled - Hidden"> 

   <p><input type="submit" value="Search &raquo;" class="button pink" /></p> 

 {/exp:low_search:form}

I'd appreciate any ideas you've got on this.

Thanks,

Stephen

Low 14 Jun 2013 22:21

As I said here, make sure the input names are "search:testfield[]", not "testfield[]".

Stephen 15 Jun 2013 03:01

Hi Low,

Sorry, I seem to have missed that a few times! Think I've got it now and my tests are running as expected, so I'm happy with this. Thanks for all your help!

For info, here's the code I'm using for this:

 
 {exp:low_search:form collection="ls_event_search" search_mode="all" result_page="/searches/test-search-results" secure="no" query="{segment_3}"} 

   <label for="testfield"><input type="checkbox" name="search:testfield[]" value="thursday" id="testfield"  />&nbsp;Thursday</label> 
   <label for="testfield"><input type="checkbox" name="search:testfield[]" value="friday" id="testfield"  />&nbsp;Friday</label> 
   <label for="testfield"><input type="checkbox" name="search:testfield[]" value="saturday" id="testfield"  />&nbsp;Saturday</label> 
   <label for="testfield"><input type="checkbox" name="search:testfield[]" value="sunday" id="testfield"  />&nbsp;Sunday</label> 

   <label for="require_all">AND/OR</label> 
   <select name="require_all"> 
     <option value="">OR</option> 
     <option value="search:testfield">AND</option> 
   </select> 

   <input type="hidden" name="status" value="Cancelled - Hidden"> 
   <input type="submit" value="Search &raquo;" /> 

 {/exp:low_search:form}

Thanks,

Stephen

Stephen 1 Mar 2013 12:59

Hi Low,

Thanks as always for going through this and giving me some ideas for workarounds.

Unfortunately, I need to keep these checkboxes as separate checkboxes, rather than have them as a checkbox group (if I've understood you correctly) due to how they're used elsewhere (from a validation and also outputting point of view).

They're currently outputted like this in the search form, though it's not just these specific checkboxes that we'd like to be able to do and/or searches for; it's the search form in general for custom field filtering:

<label><input type="checkbox" name="search:cf_event_not_normally_open" value="Yes"{if low_search_search:cf_event_not_normally_open == "Yes"} checked="checked"{/if} /> Not normally open</label> <label><input type="checkbox" name="search:cf_event_entry_charge_waived" value="Yes"{if low_search_search:cf_event_entry_charge_waived == "Yes"} checked="checked"{/if} /> Entry charge waived</label> <label><input type="checkbox" name="search:cf_event_put_on_especially" value="Yes"{if low_search_search:cf_event_put_on_especially == "Yes"} checked="checked"{/if} /> Especially put on</label>

Is the ability to do AND/OR searches on custom fields likely to be added as a new feature in the future, do you think? I think it'd be a useful feature and add flexibility to the searching, and I noticed you've got AND/OR searching (or filtering) for categories as of 2.2.0, but not sure that is quite the same thing or not as I've not used that.

Thanks,

Ste
Low 1 Mar 2013 13:56

The AND/OR searches for categories works differently, in that it targets a single field (category) and its parameters use the same principle: entry parameters between themselves always use AND.

status="open" AND category="1 or 2 or 3" AND search:field="foo" AND ... etc.

Natively, there is no status="open" AND category="1 or 2 or 3" OR search:field="foo". Allowing for that feature goes against the general paradigm of EE tags, so I'd have to think long and hard about a way to get this working in a good way.
Stephen 5 Jun 2013 11:07

Hi Low,

I keep coming back and thinking what a great feature this would be... ;-)

Ste
Low 5 Jun 2013 11:09

Noted, Stephen. :)
Bhashkar Yadav 15 Jan 2014 01:10

Hi Low,

I would like to have same feature with AND/OR condition but sorry I couldn't understand exactly what to do with this discussion.

Let me explain. My low:search form code is like:

My seach form is like:
```
 
 {exp:low_search:form} 
  Field 1: 
  <select name="search:field1[]"> 
   ##### 
  </select> 
  Field 2: 
  <select name="search:field2[]"> 
   ##### 
  </select> 
  Field 3: 
  <select name="search:field3[]"> 
   ##### 
  </select> 
  <select name="and_or"> 
   <option value="and">AND</option> 
   <option value="or">OR</option> 
  </select> 
  <input type="hidden" name="require_all" id="require_all" value="{low_search_require_all}" /> 
 {/exp:low_search:form} 
 
```
On changing and_or select box to OR, I am updating require_all value as "search:field1|search:field2|search:field3" i.e
```
 
 <input type="hidden" name="require_all" id="require_all" value="search:field1|search:field2|search:field3" /> 
 
```
But it doesn't seem working for OR. Could you please guide me where I am going wrong.

Thanks,

Low 15 Jan 2014 08:09

This is a great conversation that's separate from the main topic, so I created a new topic to continue the discussion. Please reference the new topic here: Combining AND and OR with Field Search filter