Possible to specify AND/OR custom field searches?

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
5 people like
this idea
+1
Reply
  • Low (Official Rep) March 01, 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.
    • view 6 more comments
    • Low (Official Rep) June 14, 2013 22:21
      As I said here, make sure the input names are "search:testfield[]", not "testfield[]".
    • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Low (Official Rep) March 01, 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Low,

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

    Ste
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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,
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated