Problem with multiselect type fields and RLIKE

[rocxa]

This post + pull request:
http://forum.civicrm.org/index.php/topic,32539.msg138972.html#msg138972

introduced a fix for multi-select custom fields so that searching can deal with the separator character that these fields use [[:SOH:]] [[:001:]] unhex('01') etc

This however causes problems when multi select fields have similar values.

RLIKE 'cheese' and RLIKE 'cheese-board' are both found if looking for 'cheese'

Does anyone have suggestions on a way to fix this? (without avoiding similar values)

Lines 1052-1059 of CRM/Core/BAO/Mapping.php are the culprit.

[rocxa]

Essentially something like this is needed for these fields?

RLIKE CONCAT(UNHEX('01'),$value,UNHEX('01'));

or

RLIKE "[[.SOH.]]" . $value  . "[[.SOH.]]";

I can't get either to work though as they are converted to lower case further down the line.

[rocxa]

This works for multiselects that don't have similar values with spaces in..

$value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";

But the [[::cntrl:]] bit is not retained when saving the search as a smart group.  I am guessing that goes through a different query building function somewhere else.

[rocxa]

Fixing the problem in CRM/Core/BAO/CustomQuery.php (line 419) appears to solve the problem for searching and smart groups.

Code: [Select]

$specialHTMLType = array(
              'CheckBox',
              'Multi-Select',
              'AdvMulti-Select',
              'Multi-Select State/Province',
              'Multi-Select Country',
            );
               
if (in_array($field['html_type'], $specialHTMLType)) {
 
     $val = "[[:cntrl:]]".$val."[[:cntrl:]]";
     $op  = 'RLIKE';

} else {

   if ($wildcard) {
$val = $strtolower(CRM_Core_DAO::escapeString($val));
$val = "%$val%";
$op  = 'LIKE';
   }
}
   

This problem also affects CRM/Report/Form.php (line 1452) in the same way so fix as follows   

Code: [Select]

               

      case 'mhas':
        // mhas == multiple has
        if ($value !== NULL && count($value) > 0) {
          $sqlOP = $this->getSQLOperator($op);
          $clause = "{$field['dbAlias']} REGEXP '[[:cntrl:]]" . implode('|', $value) . "[[:cntrl:]]'";
}

        break;

      case 'mnot':
        // mnot == multiple is not one of
        if ($value !== NULL && count($value) > 0) {
          $sqlOP = $this->getSQLOperator($op);
          $clause = "( {$field['dbAlias']} NOT REGEXP '[[:cntrl:]]" . implode('|', $value) . "[[:cntrl:]]' OR {$field['dbAlias']} IS NULL )";
}

        break;


Needs reviewing testing..

[Coleman Watts]

Could you turn this into a pull request? That will make it easier to review & merge into core.