From 0b38e8f106a92194ff760a31c8f9e3a35e634de4 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Fri, 11 Dec 2015 18:15:29 +0530 Subject: [PATCH] CRM-17492 fix - Query speed issue searching on transaction ID https://issues.civicrm.org/jira/browse/CRM-17492 --- CRM/Activity/Form/Search.php | 21 ++++-------- CRM/Contact/BAO/Query.php | 48 ++++++++++++++++++++++++---- CRM/Contact/Form/Search/Advanced.php | 17 ++-------- CRM/Contribute/Form/Search.php | 12 +------ 4 files changed, 51 insertions(+), 47 deletions(-) diff --git a/CRM/Activity/Form/Search.php b/CRM/Activity/Form/Search.php index db56b5735c..a424957461 100644 --- a/CRM/Activity/Form/Search.php +++ b/CRM/Activity/Form/Search.php @@ -192,20 +192,13 @@ class CRM_Activity_Form_Search extends CRM_Core_Form_Search { if (!empty($_POST)) { $this->_formValues = $this->controller->exportValues($this->_name); - foreach (array('activity_type_id', 'status_id', 'activity_subject') as $element) { - $value = CRM_Utils_Array::value($element, $this->_formValues); - if ($value) { - if (is_array($value)) { - if ($element == 'status_id') { - unset($this->_formValues[$element]); - $this->_formValues['activity_' . $element] = $value; - } - } - else { - $this->_formValues[$element] = array('LIKE' => "%$value%"); - } - } - } + $specialParams = array( + 'activity_type_id', + 'status_id', + 'activity_subject' + ); + $changeNames = array('status_id' => 'activity_status_id'); + CRM_Contact_BAO_Query::processSpecialFormValue($this->_formValues, $specialParams, $changeNames); } $this->fixFormValues(); diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 798f730e8a..f75aa7ed60 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -2137,8 +2137,8 @@ class CRM_Contact_BAO_Query { elseif ($name === 'name') { $value = $strtolower(CRM_Core_DAO::escapeString($value)); if ($wildcard) { - $value = "%$value%"; $op = 'LIKE'; + $value = self::getWildCardedValue($wildcard, $op, $value); } $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}"; $this->_where[$grouping][] = self::buildClause($wc, $op, "'$value'"); @@ -2147,8 +2147,8 @@ class CRM_Contact_BAO_Query { elseif ($name === 'current_employer') { $value = $strtolower(CRM_Core_DAO::escapeString($value)); if ($wildcard) { - $value = "%$value%"; $op = 'LIKE'; + $value = self::getWildCardedValue($wildcard, $op, $value); } $wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name"; $ceWhereClause = self::buildClause($wc, $op, @@ -2190,8 +2190,8 @@ class CRM_Contact_BAO_Query { $this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )"; $value = $strtolower(CRM_Core_DAO::escapeString($value)); if ($wildcard) { - $value = "%$value%"; $op = 'LIKE'; + $value = self::getWildCardedValue($wildcard, $op, $value); } $wc = 'civicrm_website.url'; @@ -2271,8 +2271,8 @@ class CRM_Contact_BAO_Query { $value = $strtolower($value); } if ($wildcard) { - $value = "%$value%"; $op = 'LIKE'; + $value = self::getWildCardedValue($wildcard, $op, $value); } $this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type); @@ -3302,7 +3302,7 @@ WHERE $smartGroupClause $value = $strtolower(CRM_Core_DAO::escapeString(trim($value))); if (strlen($value)) { $fieldsub = array(); - $value = "'" . $this->getWildCardedValue($wildcard, $op, $value) . "'"; + $value = "'" . self::getWildCardedValue($wildcard, $op, $value) . "'"; if ($fieldName == 'sort_name') { $wc = self::caseImportant($op) ? "LOWER(contact_a.sort_name)" : "contact_a.sort_name"; } @@ -3364,7 +3364,7 @@ WHERE $smartGroupClause $op = '='; } else { - $value = $this->getWildCardedValue($wildcard, $op, $n); + $value = self::getWildCardedValue($wildcard, $op, $n); } $this->_qill[$grouping][] = ts('Email') . " $op '$n'"; $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String'); @@ -5895,7 +5895,10 @@ AND displayRelType.is_active = 1 * * @return string */ - public function getWildCardedValue($wildcard, $op, $value) { + public static function getWildCardedValue($wildcard, $op, $value) { + if (!$value) { + return; + } if ($wildcard && $op == 'LIKE') { if (CRM_Core_Config::singleton()->includeWildCardInName && (substr($value, 0, 1) != '%')) { return "%$value%"; @@ -5909,4 +5912,35 @@ AND displayRelType.is_active = 1 } } + /** + * Process special fields of Search Form in OK (Operator in Key) format + * + * @param array $formValues + * @param array $specialFields + * Special params to be processed + * @param array $changeNames + * Array of fields whose name should be changed + */ + public static function processSpecialFormValue(&$formValues, $specialFields, $changeNames = array()) { + foreach ($specialFields as $element) { + $value = CRM_Utils_Array::value($element, $formValues); + if ($value) { + if (is_array($value)) { + if (in_array($element, $changeNames)) { + unset($formValues[$element]); + $element = $changeNames[$element]; + } + $formValues[$element] = array('IN' => $value); + } + else { + // if wildcard is already present return searchString as it is OR append and/or prepend with wildcard + $isWilcard = strstr($value, '%') ? FALSE : CRM_Core_Config::singleton()->includeWildCardInName; + $formValues[$element] = array( + 'LIKE' => self::getWildCardedValue($isWilcard, 'LIKE', $value) + ); + } + } + } + } + } diff --git a/CRM/Contact/Form/Search/Advanced.php b/CRM/Contact/Form/Search/Advanced.php index db74a57344..aca7d35e79 100644 --- a/CRM/Contact/Form/Search/Advanced.php +++ b/CRM/Contact/Form/Search/Advanced.php @@ -350,21 +350,8 @@ class CRM_Contact_Form_Search_Advanced extends CRM_Contact_Form_Search { 'contact_tags', 'preferred_communication_method', ); - foreach ($specialParams as $element) { - $value = CRM_Utils_Array::value($element, $this->_formValues); - if ($value) { - if (is_array($value)) { - if ($element == 'status_id') { - unset($this->_formValues[$element]); - $element = 'activity_' . $element; - } - $this->_formValues[$element] = array('IN' => $value); - } - elseif (strstr($value, '%')) { - $this->_formValues[$element] = array('LIKE' => $value); - } - } - } + $changeNames = array('status_id' => 'activity_status_id'); + CRM_Contact_BAO_Query::processSpecialFormValue($this->_formValues, $specialParams, $changeNames); $taglist = CRM_Utils_Array::value('contact_taglist', $this->_formValues); diff --git a/CRM/Contribute/Form/Search.php b/CRM/Contribute/Form/Search.php index 42932e5ebf..7d3d5b7205 100644 --- a/CRM/Contribute/Form/Search.php +++ b/CRM/Contribute/Form/Search.php @@ -281,17 +281,7 @@ class CRM_Contribute_Form_Search extends CRM_Core_Form_Search { 'invoice_id', 'payment_instrument_id', ); - foreach ($specialParams as $element) { - $value = CRM_Utils_Array::value($element, $this->_formValues); - if ($value) { - if (is_array($value)) { - $this->_formValues[$element] = array('IN' => $value); - } - else { - $this->_formValues[$element] = array('LIKE' => "%$value%"); - } - } - } + CRM_Contact_BAO_Query::processSpecialFormValue($this->_formValues, $specialParams); $tags = CRM_Utils_Array::value('contact_tags', $this->_formValues); if ($tags && !is_array($tags)) { -- 2.25.1