From 7bd16b05a86ce9bee5214551febde80d43fc1cd3 Mon Sep 17 00:00:00 2001 From: eileen Date: Fri, 28 Apr 2017 15:51:16 +1200 Subject: [PATCH] CRM-19821 performance improvement on the activity search. This commit removes 2 bad joins from the activity search - priority_id - this is just removed from the returnProperties as it is not rendered - activity_status_id - this is handled as a pseudoconstant rather than the bad join activity_type_id is partially handled, however, there is a component_id filter that needs to be fixed before the bad join on this field can be removed. --- CRM/Activity/BAO/Query.php | 31 ++++++++++++++++++++++++++++--- CRM/Activity/Selector/Search.php | 5 ++--- CRM/Contact/BAO/Query.php | 14 ++++++++++---- CRM/Core/DAO.php | 2 +- 4 files changed, 41 insertions(+), 11 deletions(-) diff --git a/CRM/Activity/BAO/Query.php b/CRM/Activity/BAO/Query.php index 315ef64ff4..50fd655799 100644 --- a/CRM/Activity/BAO/Query.php +++ b/CRM/Activity/BAO/Query.php @@ -75,12 +75,10 @@ class CRM_Activity_BAO_Query { } if (!empty($query->_returnProperties['activity_status_id'])) { - $query->_select['activity_status_id'] = 'activity_status.value as activity_status_id'; + $query->_select['activity_status_id'] = 'civicrm_activity.status_id as activity_status_id'; $query->_element['activity_status_id'] = 1; $query->_tables['civicrm_activity'] = 1; - $query->_tables['activity_status'] = 1; $query->_whereTables['civicrm_activity'] = 1; - $query->_whereTables['activity_status'] = 1; } if (!empty($query->_returnProperties['activity_status'])) { @@ -577,6 +575,33 @@ class CRM_Activity_BAO_Query { return $properties; } + /** + * Get the list of fields required to populate the selector. + * + * The default return properties array returns far too many fields for 'everyday use. Every field you add to this array + * kills a small kitten so add carefully. + */ + public static function selectorReturnProperties() { + $properties = array( + 'activity_id' => 1, + 'contact_type' => 1, + 'contact_sub_type' => 1, + 'sort_name' => 1, + 'display_name' => 1, + 'activity_type_id' => 1, + 'activity_subject' => 1, + 'activity_date_time' => 1, + 'activity_status_id' => 1, + 'source_contact' => 1, + 'source_record_id' => 1, + 'activity_is_test' => 1, + 'activity_campaign_id' => 1, + 'activity_engagement_level' => 1, + ); + + return $properties; + } + /** * Custom form rules. * diff --git a/CRM/Activity/Selector/Search.php b/CRM/Activity/Selector/Search.php index 22ec458a59..4e79ea2093 100644 --- a/CRM/Activity/Selector/Search.php +++ b/CRM/Activity/Selector/Search.php @@ -197,9 +197,7 @@ class CRM_Activity_Selector_Search extends CRM_Core_Selector_Base implements CRM // type of selector $this->_action = $action; $this->_query = new CRM_Contact_BAO_Query($this->_queryParams, - CRM_Activity_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_ACTIVITY, - FALSE - ), + CRM_Activity_BAO_Query::selectorReturnProperties(), NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_ACTIVITY ); @@ -285,6 +283,7 @@ class CRM_Activity_Selector_Search extends CRM_Core_Selector_Base implements CRM if (empty($result->activity_id)) { continue; } + $this->_query->convertToPseudoNames($result); // the columns we are interested in foreach (self::$_properties as $property) { diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 1aa7e7cce7..7ab4201556 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -677,7 +677,6 @@ class CRM_Contact_BAO_Query { ($name == 'parent_id') ) { CRM_Activity_BAO_Query::select($this); - continue; } // if this is a hierarchical name, we ignore it @@ -859,11 +858,11 @@ class CRM_Contact_BAO_Query { // If we have an option group defined then rather than joining the option value table in // (which is an unindexed join) we render the option value on output. // @todo - extend this to other pseudoconstants. - if ($this->pseudoConstantNameIsInReturnProperties($field)) { + if ($this->pseudoConstantNameIsInReturnProperties($field, $name)) { $pseudoFieldName = $field['pseudoconstant']['optionGroupName']; $this->_pseudoConstantsSelect[$pseudoFieldName] = array( 'pseudoField' => $field['name'], - 'idCol' => $field['name'], + 'idCol' => $name, 'field_name' => $field['name'], 'bao' => $field['bao'], 'pseudoconstant' => $field['pseudoconstant'], @@ -6431,10 +6430,12 @@ AND displayRelType.is_active = 1 * have been requested. Payment_instrument is the option groun name field value. * * @param array $field + * @param string $fieldName + * The unique name of the field - ie. the one it will be aliased to in the query. * * @return bool */ - private function pseudoConstantNameIsInReturnProperties($field) { + private function pseudoConstantNameIsInReturnProperties($field, $fieldName = NULL) { if (!isset($field['pseudoconstant']['optionGroupName'])) { return FALSE; } @@ -6442,6 +6443,11 @@ AND displayRelType.is_active = 1 if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) { return TRUE; } + if (CRM_Utils_Array::value($fieldName, $this->_returnProperties)) { + return TRUE; + } + // Is this still required - the above goes off the unique name. Test with things like + // communication_prefferences & prefix_id. if (CRM_Utils_Array::value($field['name'], $this->_returnProperties)) { return TRUE; } diff --git a/CRM/Core/DAO.php b/CRM/Core/DAO.php index c6218dccf3..b1bf5ba765 100644 --- a/CRM/Core/DAO.php +++ b/CRM/Core/DAO.php @@ -2209,7 +2209,7 @@ SELECT contact_id public static function buildOptions($fieldName, $context = NULL, $props = array()) { // If a given bao does not override this function $baoName = get_called_class(); - return CRM_Core_PseudoConstant::get($baoName, $fieldName, array(), $context); + return CRM_Core_PseudoConstant::get($baoName, $fieldName, $props, $context); } /** -- 2.25.1