Author Topic: Problem with multiselect type fields and RLIKE (Read 613 times)

rocxa · May 20, 2015, 03:00:34 am

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 · May 20, 2015, 04:20:55 am

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 · May 20, 2015, 09:23:20 am

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 · May 20, 2015, 02:46:00 pm

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 · May 27, 2015, 05:28:06 am

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

CiviCRM Community Forums (archive)

News:

Author Topic: Problem with multiselect type fields and RLIKE (Read 613 times)

rocxa

Problem with multiselect type fields and RLIKE

rocxa

Re: Problem with multiselect type fields and RLIKE

rocxa

Re: Problem with multiselect type fields and RLIKE

rocxa

Re: Problem with multiselect type fields and RLIKE

Coleman Watts

Re: Problem with multiselect type fields and RLIKE