Project

General

Profile

Actions

Feature proposal #6231

open

improve multi-select search query

Added by krileon over 7 years ago. Updated 6 months ago.

Status:
Feedback
Priority:
Normal
Assignee:
-
Target version:
Start date:
30 September 2016
Due date:
% Done:

0%

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 #1

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.

Actions #2

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?

Actions #3

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.

Actions #4

Updated by krileon over 7 years ago

  • Status changed from Assigned to Feedback
  • Assignee deleted (krileon)
Actions #5

Updated by krileon over 7 years ago

  • Target version changed from CB 2.1 to CB 2.2
Actions #6

Updated by krileon almost 6 years ago

  • Target version changed from CB 2.2 to CB 2.8
Actions #7

Updated by beat about 1 year ago

  • Target version changed from CB 2.8 to CB 2.8.1
Actions #8

Updated by beat 8 months ago

  • Target version changed from CB 2.8.1 to CB 2.8.2
Actions #9

Updated by beat 7 months ago

  • Target version changed from CB 2.8.2 to CB 2.9.0
Actions #10

Updated by beat 6 months ago

  • Target version changed from CB 2.9.0 to CB 2.9.2
Actions

Also available in: Atom PDF