Feature proposal #6231: improve multi-select search query - CB - Joomlapolis forge

Actions

Copy link

Feature proposal #6231

open

improve multi-select search query

Added by krileon about 8 years ago. Updated 3 months ago.

Status:

Feedback

Priority:

Normal

Assignee:

Target version:

CB 2.9.4

Start date:

30 September 2016

Due date:

% Done:

Estimated time:

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

multiselect_search_regexp.txt (150 Bytes) multiselect_search_regexp.txt

krileon, 01 December 2016 21:08

Actions

Copy link

Updated by krileon almost 8 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.

Actions

Copy link

Updated by krileon almost 8 years ago

File multiselect_search_regexp.txt multiselect_search_regexp.txt added

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?

Actions

Copy link

Updated by krileon almost 8 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.

Actions

Copy link