Feature proposal #6231
open
improve multi-select search query
Added by krileon over 7 years ago.
Updated 6 months ago.
Start date:
30 September 2016
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.
https://www.joomlapolis.com/forum/153-professional-member-support/234879-user-search-function-issue#286469
Files
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.
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?
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.
- Status changed from Assigned to Feedback
- Assignee deleted (
krileon)
- Target version changed from CB 2.1 to CB 2.2
- Target version changed from CB 2.2 to CB 2.8
- Target version changed from CB 2.8 to CB 2.8.1
- Target version changed from CB 2.8.1 to CB 2.8.2
- Target version changed from CB 2.8.2 to CB 2.9.0
- Target version changed from CB 2.9.0 to CB 2.9.2
Also available in: Atom
PDF
Updated by 
Updated by