From 43a96367eedb256b78638e3998ba1811894d2fee Mon Sep 17 00:00:00 2001 From: eileen Date: Tue, 30 Mar 2021 18:42:36 +1300 Subject: [PATCH] [REF] Extract portion of GroupContact load that builds the temporary table This is mostly a straight extraction. I chose to pass groupIDs in as an array as I think that creates greater flexibility and I chose to pass CRM_Utils_SQL_TempTable in rather than load it in the function because I have some ideas I'm working through about how that might also provide more flexibilty Those things can be changed later if we want as it IS a protected function --- CRM/Contact/BAO/GroupContactCache.php | 197 +++++++++++++++----------- CRM/Contact/BAO/SearchCustom.php | 2 +- 2 files changed, 112 insertions(+), 87 deletions(-) diff --git a/CRM/Contact/BAO/GroupContactCache.php b/CRM/Contact/BAO/GroupContactCache.php index defc740039..3e75e85133 100644 --- a/CRM/Contact/BAO/GroupContactCache.php +++ b/CRM/Contact/BAO/GroupContactCache.php @@ -9,6 +9,7 @@ +--------------------------------------------------------------------+ */ +use Civi\Api4\Group; use Civi\Api4\Query\SqlExpression; /** @@ -418,11 +419,12 @@ WHERE id IN ( $groupIDs ) * @param bool $force * Should we force a search through. * + * @throws \API_Exception * @throws \CRM_Core_Exception + * @throws \CiviCRM_API3_Exception */ - public static function load(&$group, $force = FALSE) { - $groupID = $group->id; - $savedSearchID = $group->saved_search_id; + public static function load($group, $force = FALSE) { + $groupID = (int) $group->id; if (array_key_exists($groupID, self::$_alreadyLoaded) && !$force) { return; } @@ -435,92 +437,15 @@ WHERE id IN ( $groupIDs ) return; } - $customClass = NULL; - if ($savedSearchID) { - $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($savedSearchID); - $groupID = CRM_Utils_Type::escape($groupID, 'Integer'); - - $excludeClause = "NOT IN ( - SELECT contact_id FROM civicrm_group_contact - WHERE civicrm_group_contact.status = 'Removed' - AND civicrm_group_contact.group_id = $groupID )"; - $addSelect = "$groupID AS group_id"; - - if (!empty($ssParams['api_entity'])) { - $sql = self::getApiSQL($ssParams, $addSelect, $excludeClause); - } - else { - // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance - if (!empty($ssParams)) { - CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams); - } - if (isset($ssParams['customSearchID'])) { - $sql = self::getCustomSearchSQL($savedSearchID, $ssParams, $addSelect, $excludeClause); - } - else { - $sql = self::getQueryObjectSQL($savedSearchID, $ssParams, $addSelect, $excludeClause); - } - } - } - - $groupContactsTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('gccache')->setMemory(); + $groupContactsTempTable = CRM_Utils_SQL_TempTable::build() + ->setCategory('gccache') + ->setMemory(); + self::buildGroupContactTempTable([$groupID], $groupContactsTempTable); $tempTable = $groupContactsTempTable->getName(); - $groupContactsTempTable->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)'); - - if (!empty($sql)) { - $contactQueries[] = $sql; - } - // lets also store the records that are explicitly added to the group - // this allows us to skip the group contact LEFT JOIN - $contactQueries[] = - "SELECT $groupID as group_id, contact_id as contact_id - FROM civicrm_group_contact - WHERE civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID "; - - self::clearGroupContactCache($groupID); - - foreach ($contactQueries as $contactQuery) { - CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) {$contactQuery}"); - } - - CRM_Core_DAO::reenableFullGroupByMode(); - - if ($group->children) { - - // Store a list of contacts who are removed from the parent group - $sqlContactsRemovedFromGroup = " -SELECT contact_id -FROM civicrm_group_contact -WHERE civicrm_group_contact.status = 'Removed' -AND civicrm_group_contact.group_id = $groupID "; - $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup); - $removed_contacts = []; - while ($dao->fetch()) { - $removed_contacts[] = $dao->contact_id; - } - - $childrenIDs = explode(',', $group->children); - foreach ($childrenIDs as $childID) { - $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE); - // Unset each contact that is removed from the parent group - foreach ($removed_contacts as $removed_contact) { - unset($contactIDs[$removed_contact]); - } - if (empty($contactIDs)) { - // This child group has no contact IDs so we don't need to add them to - continue; - } - $values = []; - foreach ($contactIDs as $contactID => $dontCare) { - $values[] = "({$groupID},{$contactID})"; - } - $str = implode(',', $values); - CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) VALUES $str"); - } - } // grab a lock so other processes don't compete and do the same query $lock = Civi::lockManager()->acquire("data.core.group.{$groupID}"); + if (!$lock->isAcquired()) { // this can cause inconsistent results since we don't know if the other process // will fill up the cache before our calling routine needs it. @@ -695,7 +620,7 @@ ORDER BY gc.contact_id, g.children * @param string $excludeClause * * @return string - * @throws \Exception + * @throws CRM_Core_Exception */ protected static function getCustomSearchSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) { $searchSQL = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs(); @@ -758,4 +683,104 @@ ORDER BY gc.contact_id, g.children return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}"; } + /** + * Build a temporary table for the contacts in the specified group. + * + * @param array $groupIDs + * Currently only one id is build but this has been written + * to make it easy to switch to multiple. + * @param CRM_Utils_SQL_TempTable $tempTableObject + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + * @throws \CiviCRM_API3_Exception + */ + protected static function buildGroupContactTempTable(array $groupIDs, $tempTableObject): void { + $group = Group::get(FALSE)->addWhere('id', 'IN', $groupIDs) + ->setSelect(['saved_search_id', 'children'])->execute()->first(); + $groupID = (int) $group['id']; + + $customClass = NULL; + if ($group['saved_search_id']) { + $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($group['saved_search_id']); + + $excludeClause = "NOT IN ( + SELECT contact_id FROM civicrm_group_contact + WHERE civicrm_group_contact.status = 'Removed' + AND civicrm_group_contact.group_id = $groupID )"; + $addSelect = "$groupID AS group_id"; + + if (!empty($ssParams['api_entity'])) { + $sql = self::getApiSQL($ssParams, $addSelect, $excludeClause); + } + else { + // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance + if (!empty($ssParams)) { + CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams); + } + if (isset($ssParams['customSearchID'])) { + $sql = self::getCustomSearchSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause); + } + else { + $sql = self::getQueryObjectSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause); + } + } + } + + $tempTableName = $tempTableObject->getName(); + $tempTableObject->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)'); + + if (!empty($sql)) { + $contactQueries[] = $sql; + } + // lets also store the records that are explicitly added to the group + // this allows us to skip the group contact LEFT JOIN + $contactQueries[] = + "SELECT $groupID as group_id, contact_id as contact_id + FROM civicrm_group_contact + WHERE civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID "; + + self::clearGroupContactCache($groupID); + + foreach ($contactQueries as $contactQuery) { + CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) {$contactQuery}"); + } + + CRM_Core_DAO::reenableFullGroupByMode(); + + if ($group['children']) { + + // Store a list of contacts who are removed from the parent group + $sqlContactsRemovedFromGroup = " +SELECT contact_id +FROM civicrm_group_contact +WHERE civicrm_group_contact.status = 'Removed' +AND civicrm_group_contact.group_id = $groupID "; + $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup); + $removed_contacts = []; + while ($dao->fetch()) { + $removed_contacts[] = $dao->contact_id; + } + + $childrenIDs = explode(',', $group['children']); + foreach ($childrenIDs as $childID) { + $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE); + // Unset each contact that is removed from the parent group + foreach ($removed_contacts as $removed_contact) { + unset($contactIDs[$removed_contact]); + } + if (empty($contactIDs)) { + // This child group has no contact IDs so we don't need to add them to + continue; + } + $values = []; + foreach ($contactIDs as $contactID => $dontCare) { + $values[] = "({$groupID},{$contactID})"; + } + $str = implode(',', $values); + CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) VALUES $str"); + } + } + } + } diff --git a/CRM/Contact/BAO/SearchCustom.php b/CRM/Contact/BAO/SearchCustom.php index fd18da4430..5e4ea3174b 100644 --- a/CRM/Contact/BAO/SearchCustom.php +++ b/CRM/Contact/BAO/SearchCustom.php @@ -87,7 +87,7 @@ class CRM_Contact_BAO_SearchCustom { * @param int $ssID * * @return CRM_Contact_Form_Search_Custom_Base - * @throws Exception + * @throws CRM_Core_Exception */ public static function customClass($csID, $ssID) { list($customSearchID, $customSearchClass, $formValues) = self::details($csID, $ssID); -- 2.25.1