API4 "where" doesn't fully work for checkbox/multi-select options
Overview
If I want to search for records which contain a particular option value (or values) in a multi-value custom field, there's currently no clean way to do that using APIv4's "get" action.
This limitation of API4's "where" clauses may also apply to "update" and "delete" actions, but I only investigated "get".
Reproduction steps
- Create an option group with a couple options. Use that option group in creating a custom checkbox or multiple-select field on Contacts.
- Create a contact who has two options selected in that custom field.
- Try to use API v4 to "get" that contact, using one or both of the option values in that custom field in the "where" parameter.
You can run through these reproduction steps using this code.
Current behaviour
Records containing specific value options cannot be found successfully, except perhaps by hackily using "LIKE" with wildcards and Civi's VALUE_SEPARATOR character. In some cases when there is only a single value in the multi-value field, and you specify that option in the "where" parameter, the API will return the record, but looking at the generated SQL I think this is a fluke of collation or something.
Expected behaviour
When you use the "=" operator and a single option value in the "where" parameter, the API should return records that have exactly (and only) that one option selected.
Beyond that, we need to decide how it should work. I should be able to search for records where options A and C are selected, but not B. There might need to be a pair of custom operators, "contains" and "doesn't contain", for this purpose. These would translate into SQL LIKE or RLIKE comparisons using the VALUE_SEPARATOR. Note that there are currently some issues with RLIKE in Advanced Search as I note in #1967 (closed).