From 4243847f916f125a8b2e4671a7a7eae762546577 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 1 Jul 2013 15:13:32 -0700 Subject: [PATCH] CRM-12638 Optimize filling prevNext cache and displaying results ---------------------------------------- * CRM-12638: Optimize Prev/Next Cache and search interaction http://issues.civicrm.org/jira/browse/CRM-12638 --- CRM/Contact/BAO/Query.php | 95 +++++++++++---------------------------- CRM/Contact/Selector.php | 48 ++++++++++---------- 2 files changed, 50 insertions(+), 93 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 756e672c26..7cc40b691b 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4066,13 +4066,6 @@ civicrm_relationship.start_date > {$today} } $this->generatePermissionClause($onlyDeleted, $count); - list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts); - - //additional from clause should be w/ proper joins. - if ($additionalFromClause) { - $from .= "\n" . $additionalFromClause; - } - if (empty($where)) { $where = "WHERE $this->_permissionWhereClause"; } @@ -4139,7 +4132,6 @@ civicrm_relationship.start_date > {$today} } } - $doOpt = TRUE; // hack for order clause if ($order) { $fieldStr = trim(str_replace('ORDER BY', '', $order)); @@ -4170,72 +4162,22 @@ civicrm_relationship.start_date > {$today} $this->_whereTables["civicrm_email"] = 1; $order = str_replace($field, "civicrm_email.{$field}", $order); break; - - default: - $doOpt = FALSE; } + $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode); + $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode); } } - if ($rowCount > 0 && $offset >= 0) { $limit = " LIMIT $offset, $rowCount "; + } + } - // ok here is a first hack at an optimization, lets get all the contact ids - // that are restricted and we'll then do the final clause with it - // CRM-5954 - if (isset($this->_distinctComponentClause)) { - if (strpos($this->_distinctComponentClause, 'DISTINCT') == FALSE) { - $limitSelect = "SELECT DISTINCT {$this->_distinctComponentClause}"; - } - else { - $limitSelect = "SELECT {$this->_distinctComponentClause}"; - } - } - else { - $limitSelect = 'SELECT DISTINCT contact_a.id as id'; - } - - if ($doOpt) { - $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, - $this->_primaryLocation, $this->_mode - ); - - if ($additionalFromClause) { - $this->_simpleFromClause .= "\n" . $additionalFromClause; - } - // if we are doing a transform, do it here - // CRM-7969 - $having = NULL; - if ($this->_displayRelationshipType) { - $this->filterRelatedContacts($this->_simpleFromClause, $where, $having); - } + list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts); - $limitQuery = "$limitSelect {$this->_simpleFromClause} $where $groupBy $order $limit"; - $limitDAO = CRM_Core_DAO::executeQuery($limitQuery); - $limitIDs = array(); - while ($limitDAO->fetch()) { - if ($limitDAO->id) { - $limitIDs[] = $limitDAO->id; - } - } - if (empty($limitIDs)) { - $limitClause = ' AND ( 0 ) '; - } - else { - if (isset($this->_distinctComponentClause)) { - $limitClause = " AND {$this->_distinctComponentClause} IN ( "; - } - else { - $limitClause = ' AND contact_a.id IN ( '; - } - $limitClause .= implode(',', $limitIDs) . ' ) '; - } - $where .= $limitClause; - // reset limit clause since we already restrict what records we want - $limit = NULL; - } - } + //additional from clause should be w/ proper joins. + if ($additionalFromClause) { + $from .= "\n" . $additionalFromClause; } // if we are doing a transform, do it here @@ -4259,8 +4201,6 @@ civicrm_relationship.start_date > {$today} return CRM_Core_DAO::singleValueQuery($query); } - // crm_core_error::debug('$query', $query); exit; - $dao = CRM_Core_DAO::executeQuery($query); if ($groupContacts) { $ids = array(); @@ -4273,6 +4213,25 @@ civicrm_relationship.start_date > {$today} return $dao; } + /** + * Fetch a list of contacts from the prev/next cache for displaying a search results page + * + * @param string $cacheKey + * @param int $offset + * @param int $rowCount + * @param bool $includeContactIds + * @return CRM_Core_DAO + */ + function getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds) { + $this->_includeContactIds = $includeContactIds; + list($select, $from, ) = $this->query(); + $from = " FROM civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON contact_a.id = pnc.entity_id1 AND pnc.cacheKey = '$cacheKey' " . substr($from, 31); + $order = " ORDER BY pnc.id"; + $limit = " LIMIT $offset, $rowCount"; + $query = "$select $from $order $limit"; + return CRM_Core_DAO::executeQuery($query); + } + /** * Populate $this->_permissionWhereClause with permission related clause and update other * query related properties. diff --git a/CRM/Contact/Selector.php b/CRM/Contact/Selector.php index c649c3cf06..1ea38a5e40 100644 --- a/CRM/Contact/Selector.php +++ b/CRM/Contact/Selector.php @@ -506,7 +506,13 @@ class CRM_Contact_Selector extends CRM_Core_Selector_Base implements CRM_Core_Se // note the formvalues were given by CRM_Contact_Form_Search to us // and contain the search criteria (parameters) // note that the default action is basic - $result = $this->_query->searchQuery($offset, $rowCount, $sort, FALSE, $includeContactIds); + if ($rowCount) { + $cacheKey = $this->buildPrevNextCache($sort); + $result = $this->_query->getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds); + } + else { + $result = $this->_query->searchQuery($offset, $rowCount, $sort, FALSE, $includeContactIds); + } // process the result of the query $rows = array(); @@ -788,16 +794,20 @@ class CRM_Contact_Selector extends CRM_Core_Selector_Base implements CRM_Core_Se } } - $this->buildPrevNextCache($sort); - return $rows; } function buildPrevNextCache($sort) { - $cacheKey = CRM_Utils_Array::value('qfKey', $this->_formValues); + $cacheKey = 'civicrm search ' . $this->_key; + + // Get current page requested + $pageNum = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject); + // When starting from scratch, clear any old cache + if (!$pageNum) { + CRM_Core_BAO_PrevNextCache::deleteItem(NULL, $cacheKey, 'civicrm_contact'); + $pageNum = 1; + } - //for prev/next pagination - $pageNum = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject, FALSE, 1); $pageSize = CRM_Utils_Request::retrieve('crmRowCount', 'Integer', CRM_Core_DAO::$_nullObject, FALSE, 50); $firstRecord = ($pageNum - 1) * $pageSize; @@ -805,21 +815,17 @@ class CRM_Contact_Selector extends CRM_Core_Selector_Base implements CRM_Core_Se $sortByCharacter = CRM_Utils_Request::retrieve('sortByCharacter', 'String', CRM_Core_DAO::$_nullObject); //for text field pagination selection save - $countRow = CRM_Core_BAO_PrevNextCache::getCount("%civicrm search {$cacheKey}%", NULL, "entity_table = 'civicrm_contact'", "LIKE"); + $countRow = CRM_Core_BAO_PrevNextCache::getCount($cacheKey, NULL, "entity_table = 'civicrm_contact'"); // $sortByCharacter triggers a refresh in the prevNext cache - if ($sortByCharacter) { - $cacheKeyCharacter = "civicrm search {$cacheKey}_alphabet"; - if ($sortByCharacter == 'all') { - //delete the alphabet key corresponding records in prevnext_cache - CRM_Core_BAO_PrevNextCache::deleteItem(NULL, $cacheKeyCharacter, 'civicrm_contact'); - $cacheKeyCharacter = NULL; - } - $this->fillupPrevNextCache($sort, $cacheKeyCharacter); + if ($sortByCharacter && $sortByCharacter != 'all') { + $cacheKey .= "_alphabet"; + $this->fillupPrevNextCache($sort, $cacheKey); } elseif ($firstRecord >= $countRow) { - $this->fillupPrevNextCache($sort, NULL, $countRow, $firstRecord + 500); + $this->fillupPrevNextCache($sort, $cacheKey, $countRow, $firstRecord + 500); } + return $cacheKey; } function addActions(&$rows) { @@ -902,15 +908,7 @@ class CRM_Contact_Selector extends CRM_Core_Selector_Base implements CRM_Core_Se * @param int $start * @param int $end */ - function fillupPrevNextCache($sort, $cacheKey = NULL, $start = 0, $end = 500) { - if (!$cacheKey) { - $cacheKey = "civicrm search {$this->_key}"; - } - - // When starting from zero, clear any old cache - if ($start == 0) { - CRM_Core_BAO_PrevNextCache::deleteItem(NULL, $cacheKey, 'civicrm_contact'); - } + function fillupPrevNextCache($sort, $cacheKey, $start = 0, $end = 500) { // For custom searches, use the contactIDs method if (is_a($this, 'CRM_Contact_Selector_Custom')) { -- 2.25.1