Prevent un-workable searches in search builder (was search builder improvements)
Earlier there was a requirement where we need to remove specific MySQL operators which are not valid for specific data type e.g. String type doesn't work with >, <, <= and >= operators. And here's the fix https://github.com/civicrm/civicrm-core/commit/759094bdfa40531e4ec0cf0a644d9edd6b9247cf done earlier as per which it is fetching all columns which are String type and passing them into a separate JSON string identified as
stringFields and another formatted list of valid string operators as
stringOperators. Similarly to identify date fields and to do a specific operation on such fields we are passing another variable
dateFields. These JSON object in JS are later used to specific operations. Now on the other hand, recently, I encountered another issue where the Boolean type columns don't work with IS EMPTY/IS NOT EMPTY operator and eventually leads to DB syntax error. So if I follow the same approach I need to assign another JSON string, which won't be an ideal fix. This lead me to do following 3 changes to improve the code:
- Assign a single list of all data columns which are either Boolean, String or Date (as currently, these are the only kind that needs attention)
- Use this list (as
fieldTypes) in JS to filter the operator or do field specific operations like covert a input search field to datepicker if the chosen search field is date kind.
- Reduce list of variable assignment.