Feature proposal #6231
open
improve multi-select search query
0%
Description
When searching a mult-select (or multi-checkbox) field it adds each value selected as a LIKE usage to the query. This results in usage like the below.
AND (((ue.`cb_checkboxmultiple` LIKE '%Value 1%') OR (ue.`cb_checkboxmultiple` LIKE '%Value 4%') OR (ue.`cb_checkboxmultiple` LIKE '%Value 8%')))
The problem with this is if 1 value contains the value of another you end up getting incorrect results. For example I search Value 1 it will also match Value 11, Value 111, etc.. This should convert the |*| and do an IN usage instead with a single AND.
Files
Updated by krileon over 7 years ago
This behavior is a part of cbSqlQueryPart. Looks like a custom formula needs to be sent to cbSqlQueryPart instead of just sending an array of a bunch of equal to usages.
Updated by krileon over 7 years ago
REGEXP works to match this fine. The below expression can do this.
SEE ATTACHMENT
Can even handle matching multiple values as follows.
SEE ATTACHMENT
Maybe instead of dozens of LIKE conditions there just be 1 REGEXP for the field?
Updated by krileon over 7 years ago
Index benefits possibly lost with REGEXP usage. Explain provides "Using where; Using temporary; Using filesort;". Sample query performance: 0.0047s average with LIKE and 0.0072s average with REGEXP.
Updated by krileon over 7 years ago
- Status changed from Assigned to Feedback
- Assignee deleted (
krileon)
Updated by krileon over 7 years ago
- Target version changed from CB 2.1 to CB 2.2
Updated by krileon almost 6 years ago
- Target version changed from CB 2.2 to CB 2.8
Updated by