Project

General

Profile

Actions
Copy link

Feature proposal #6231

closed

improve multi-select search query

Feature proposal #6231: improve multi-select search query

Added by krileon almost 10 years ago. Updated 3 months ago.

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

100%

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

Updated by krileon almost 10 years ago Actions
Copy link
 #1

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 almost 10 years ago Actions
Copy link
 #2

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 almost 10 years ago Actions
Copy link
 #3

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 almost 10 years ago Actions
Copy link
 #4

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

Updated by krileon almost 10 years ago Actions
Copy link
 #5

  • Target version changed from CB 2.1 to CB 2.2

Updated by krileon about 8 years ago Actions
Copy link
 #6

  • Target version changed from CB 2.2 to CB 2.8

Updated by beat over 3 years ago Actions
Copy link
 #7

  • Target version changed from CB 2.8 to CB 2.8.1

Updated by beat about 3 years ago Actions
Copy link
 #8

  • Target version changed from CB 2.8.1 to CB 2.8.2

Updated by beat almost 3 years ago Actions
Copy link
 #9

  • Target version changed from CB 2.8.2 to CB 2.9.0

Updated by beat almost 3 years ago Actions
Copy link
 #10

  • Target version changed from CB 2.9.0 to CB 2.9.2

Updated by beat over 2 years ago Actions
Copy link
 #11

  • Target version changed from CB 2.9.2 to CB 2.9.3

Updated by beat about 2 years ago Actions
Copy link
 #12

  • Target version changed from CB 2.9.3 to CB 2.10.0

Updated by beat over 1 year ago Actions
Copy link
 #13

  • Target version changed from CB 2.10.0 to CB 2.11.0

Updated by beat 11 months ago Actions
Copy link
 #14

  • Target version changed from CB 2.11.0 to CB 2.12.0

Updated by krileon 3 months ago Actions
Copy link
 #15

  • Status changed from Feedback to Rejected
  • Target version deleted (CB 2.12.0)
  • % Done changed from 0 to 100

No real solution here without a complete rewrite of how they're stored. Likely to JSON and have to do JSON extract queries. Leave as is as once migrated to CB 3.0 they'll be stored as a value per-row in Joomla's user field storage and be more easily searchable.

Actions
Copy link

Also available in: PDF Atom