CRM/Contact/BAO/GroupContactCache.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 use Civi\API\Request;
  13 use Civi\Api4\Group;
  14 use Civi\Api4\Query\Api4SelectQuery;
  15 use Civi\Api4\Query\SqlExpression;
  16
  17 /**
  18  *
  19  * @package CRM
  20  * @copyright CiviCRM LLC https://civicrm.org/licensing
  21  */
  22 class CRM_Contact_BAO_GroupContactCache extends CRM_Contact_DAO_GroupContactCache {
  23
  24   /**
  25    * Get a list of caching modes.
  26    *
  27    * @return array
  28    */
  29   public static function getModes(): array {
  30     return [
  31       // Flush expired caches in response to user actions.
  32       'opportunistic' => ts('Opportunistic Flush'),
  33       // Flush expired caches via background cron jobs.
  34       'deterministic' => ts('Cron Flush'),
  35     ];
  36   }
  37
  38   /**
  39    * Check to see if we have cache entries for this group.
  40    *
  41    * If not, regenerate, else return.
  42    *
  43    * @param array $groupIDs
  44    *   Of group that we are checking against.
  45    *
  46    * @return bool
  47    *   TRUE if we did not regenerate, FALSE if we did
  48    */
  49   public static function check($groupIDs): bool {
  50     if (empty($groupIDs)) {
  51       return TRUE;
  52     }
  53
  54     return self::loadAll($groupIDs);
  55   }
  56
  57   /**
  58    * Formulate the query to see which groups needs to be refreshed.
  59    *
  60    * The calculation is based on their cache date and the smartGroupCacheTimeOut
  61    *
  62    * @param string $groupIDClause
  63    *   The clause which limits which groups we need to evaluate.
  64    * @param bool $includeHiddenGroups
  65    *   Hidden groups are excluded by default.
  66    *
  67    * @return string
  68    *   the sql query which lists the groups that need to be refreshed
  69    */
  70   public static function groupRefreshedClause($groupIDClause = NULL, $includeHiddenGroups = FALSE): string {
  71     $smartGroupCacheTimeoutDateTime = self::getCacheInvalidDateTime();
  72
  73     $query = "
  74 SELECT  g.id
  75 FROM    civicrm_group g
  76 WHERE   ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL )
  77 AND     g.is_active = 1
  78 AND (
  79   g.cache_date IS NULL
  80   OR cache_date <= $smartGroupCacheTimeoutDateTime
  81 )";
  82
  83     if (!$includeHiddenGroups) {
  84       $query .= "AND (g.is_hidden = 0 OR g.is_hidden IS NULL)";
  85     }
  86
  87     if (!empty($groupIDClause)) {
  88       $query .= " AND ( $groupIDClause ) ";
  89     }
  90
  91     return $query;
  92   }
  93
  94   /**
  95    * Check to see if a group has been refreshed recently.
  96    *
  97    * This is primarily used in a locking scenario when some other process might have refreshed things underneath
  98    * this process
  99    *
 100    * @param int $groupID
 101    *   The group ID.
 102    * @param bool $includeHiddenGroups
 103    *   Hidden groups are excluded by default.
 104    *
 105    * @return string
 106    *   the sql query which lists the groups that need to be refreshed
 107    */
 108   public static function shouldGroupBeRefreshed($groupID, $includeHiddenGroups = FALSE) {
 109     $query = self::groupRefreshedClause("g.id = %1", $includeHiddenGroups);
 110     $params = [1 => [$groupID, 'Integer']];
 111
 112     // if the query returns the group ID, it means the group is a valid candidate for refreshing
 113     return CRM_Core_DAO::singleValueQuery($query, $params);
 114   }
 115
 116   /**
 117    * Check to see if we have cache entries for this group.
 118    *
 119    * if not, regenerate, else return
 120    *
 121    * @param array|null $groupIDs groupIDs of group that we are checking against
 122    *                           if empty, all groups are checked
 123    * @param int $limit
 124    *   Limits the number of groups we evaluate.
 125    *
 126    * @return bool
 127    *   TRUE if we did not regenerate, FALSE if we did
 128    */
 129   public static function loadAll($groupIDs = NULL, $limit = 0) {
 130     if ($groupIDs) {
 131       // Passing a single value is deprecated.
 132       $groupIDs = (array) $groupIDs;
 133     }
 134
 135     $processGroupIDs = self::getGroupsNeedingRefreshing($groupIDs, $limit);
 136
 137     if (!empty($processGroupIDs)) {
 138       self::add($processGroupIDs);
 139     }
 140     return TRUE;
 141   }
 142
 143   /**
 144    * Build the smart group cache for given groups.
 145    *
 146    * @param array $groupIDs
 147    */
 148   public static function add($groupIDs) {
 149     $groupIDs = (array) $groupIDs;
 150
 151     foreach ($groupIDs as $groupID) {
 152       // first delete the current cache
 153       $params = [['group', 'IN', [$groupID], 0, 0]];
 154       // the below call updates the cache table as a byproduct of the query
 155       CRM_Contact_BAO_Query::apiQuery($params, ['contact_id'], NULL, NULL, 0, 0, FALSE);
 156     }
 157   }
 158
 159   /**
 160    * Store values into the group contact cache.
 161    *
 162    * @todo review use of INSERT IGNORE. This function appears to be slower that inserting
 163    * with a left join. Also, 200 at once seems too little.
 164    *
 165    * @param array $groupID
 166    * @param array $values
 167    */
 168   public static function store($groupID, &$values) {
 169     $processed = FALSE;
 170
 171     // sort the values so we put group IDs in front and hence optimize
 172     // mysql storage (or so we think) CRM-9493
 173     sort($values);
 174
 175     // to avoid long strings, lets do BULK_INSERT_COUNT values at a time
 176     while (!empty($values)) {
 177       $processed = TRUE;
 178       $input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT);
 179       $str = implode(',', $input);
 180       $sql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES $str;";
 181       CRM_Core_DAO::executeQuery($sql);
 182     }
 183     self::updateCacheTime($groupID, $processed);
 184   }
 185
 186   /**
 187    * Change the cache_date.
 188    *
 189    * @param array $groupID
 190    * @param bool $processed
 191    *   Whether the cache data was recently modified.
 192    */
 193   public static function updateCacheTime($groupID, $processed) {
 194     // only update cache entry if we had any values
 195     if ($processed) {
 196       // also update the group with cache date information
 197       $now = date('YmdHis');
 198     }
 199     else {
 200       $now = 'null';
 201     }
 202
 203     $groupIDs = implode(',', $groupID);
 204     $sql = "
 205 UPDATE civicrm_group
 206 SET    cache_date = $now
 207 WHERE  id IN ( $groupIDs )
 208 ";
 209     CRM_Core_DAO::executeQuery($sql);
 210   }
 211
 212   /**
 213    * Function to clear group contact cache and reset the corresponding
 214    *  group's cache and refresh date
 215    *
 216    * @param int $groupID
 217    *
 218    */
 219   public static function clearGroupContactCache($groupID): void {
 220     $transaction = new CRM_Core_Transaction();
 221     $query = "
 222     DELETE  g
 223       FROM  civicrm_group_contact_cache g
 224       WHERE  g.group_id = %1 ";
 225
 226     $update = "
 227   UPDATE civicrm_group g
 228     SET    cache_date = null
 229     WHERE  id = %1 ";
 230
 231     $params = [
 232       1 => [$groupID, 'Integer'],
 233     ];
 234
 235     CRM_Core_DAO::executeQuery($query, $params);
 236     // also update the cache_date for these groups
 237     CRM_Core_DAO::executeQuery($update, $params);
 238
 239     $transaction->commit();
 240   }
 241
 242   /**
 243    * Refresh the smart group cache tables.
 244    *
 245    * This involves clearing out any aged entries (based on the site timeout setting) and resetting the time outs.
 246    *
 247    * This function should be called via the opportunistic or deterministic cache refresh function to make the intent
 248    * clear.
 249    */
 250   protected static function flushCaches() {
 251     try {
 252       $lock = self::getLockForRefresh();
 253     }
 254     catch (CRM_Core_Exception $e) {
 255       // Someone else is kindly doing the refresh for us right now.
 256       return;
 257     }
 258     $params = [1 => [self::getCacheInvalidDateTime(), 'String']];
 259     $groupsDAO = CRM_Core_DAO::executeQuery("SELECT id FROM civicrm_group WHERE cache_date <= %1", $params);
 260     $expiredGroups = [];
 261     while ($groupsDAO->fetch()) {
 262       $expiredGroups[] = $groupsDAO->id;
 263     }
 264     if (!empty($expiredGroups)) {
 265       $expiredGroups = implode(',', $expiredGroups);
 266       CRM_Core_DAO::executeQuery("DELETE FROM civicrm_group_contact_cache WHERE group_id IN ({$expiredGroups})");
 267
 268       // Clear these out without resetting them because we are not building caches here, only clearing them,
 269       // so the state is 'as if they had never been built'.
 270       CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL WHERE id IN ({$expiredGroups})");
 271     }
 272     $lock->release();
 273   }
 274
 275   /**
 276    * Check if the refresh is already initiated.
 277    *
 278    * We have 2 imperfect methods for this:
 279    *   1) a static variable in the function. This works fine within a request
 280    *   2) a mysql lock. This works fine as long as CiviMail is not running, or if mysql is version 5.7+
 281    *
 282    * Where these 2 locks fail we get 2 processes running at the same time, but we have at least minimised that.
 283    *
 284    * @return \Civi\Core\Lock\LockInterface
 285    * @throws \CRM_Core_Exception
 286    */
 287   protected static function getLockForRefresh() {
 288     if (!isset(Civi::$statics[__CLASS__]['is_refresh_init'])) {
 289       Civi::$statics[__CLASS__] = ['is_refresh_init' => FALSE];
 290     }
 291
 292     if (Civi::$statics[__CLASS__]['is_refresh_init']) {
 293       throw new CRM_Core_Exception('A refresh has already run in this process');
 294     }
 295     $lock = Civi::lockManager()->acquire('data.core.group.refresh');
 296     if ($lock->isAcquired()) {
 297       Civi::$statics[__CLASS__]['is_refresh_init'] = TRUE;
 298       return $lock;
 299     }
 300     throw new CRM_Core_Exception('Mysql lock unavailable');
 301   }
 302
 303   /**
 304    * Do an opportunistic cache refresh if the site is configured for these.
 305    *
 306    * Sites that do not run the smart group clearing cron job should refresh the
 307    * caches on demand. The user session will be forced to wait so it is less
 308    * ideal.
 309    */
 310   public static function opportunisticCacheFlush(): void {
 311     if (Civi::settings()->get('smart_group_cache_refresh_mode') === 'opportunistic') {
 312       self::flushCaches();
 313     }
 314   }
 315
 316   /**
 317    * Do a forced cache refresh.
 318    *
 319    * This function is appropriate to be called by system jobs & non-user sessions.
 320    */
 321   public static function deterministicCacheFlush() {
 322     if (self::smartGroupCacheTimeout() == 0) {
 323       CRM_Core_DAO::executeQuery("TRUNCATE civicrm_group_contact_cache");
 324       CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL");
 325     }
 326     else {
 327       self::flushCaches();
 328     }
 329   }
 330
 331   /**
 332    * Remove one or more contacts from the smart group cache.
 333    *
 334    * @param int|array $cid
 335    * @param null $groupId
 336    *
 337    * @return bool
 338    *   TRUE if successful.
 339    * @throws \CRM_Core_Exception
 340    */
 341   public static function removeContact($cid, $groupId = NULL) {
 342     $cids = [];
 343     // sanitize input
 344     foreach ((array) $cid as $c) {
 345       $cids[] = CRM_Utils_Type::escape($c, 'Integer');
 346     }
 347     if ($cids) {
 348       $condition = count($cids) == 1 ? "= {$cids[0]}" : "IN (" . implode(',', $cids) . ")";
 349       if ($groupId) {
 350         $condition .= " AND group_id = " . CRM_Utils_Type::escape($groupId, 'Integer');
 351       }
 352       $sql = "DELETE FROM civicrm_group_contact_cache WHERE contact_id $condition";
 353       CRM_Core_DAO::executeQuery($sql);
 354       return TRUE;
 355     }
 356     return FALSE;
 357   }
 358
 359   /**
 360    * Load the smart group cache for a saved search.
 361    *
 362    * @param object $group
 363    *   The smart group that needs to be loaded.
 364    * @param bool $force
 365    *   deprecated parameter = Should we force a search through.
 366    *
 367    * @throws \API_Exception
 368    * @throws \CRM_Core_Exception
 369    * @throws \CiviCRM_API3_Exception
 370    */
 371   public static function load($group, $force = FALSE) {
 372     $groupID = (int) $group->id;
 373     if ($force) {
 374       CRM_Core_Error::deprecatedWarning('use invalidate group contact cache first.');
 375       self::invalidateGroupContactCache($group->id);
 376     }
 377
 378     // FIXME: some other process could have actually done the work before we got here,
 379     // Ensure that work needs to be done before continuing
 380     if (!self::shouldGroupBeRefreshed($groupID, TRUE)) {
 381       return;
 382     }
 383
 384     // grab a lock so other processes don't compete and do the same query
 385     $lock = Civi::lockManager()->acquire("data.core.group.{$groupID}");
 386     if (!$lock->isAcquired()) {
 387       // this can cause inconsistent results since we don't know if the other process
 388       // will fill up the cache before our calling routine needs it.
 389       // however this routine does not return the status either, so basically
 390       // its a "lets return and hope for the best"
 391       return;
 392     }
 393
 394     $groupContactsTempTable = CRM_Utils_SQL_TempTable::build()
 395       ->setCategory('gccache')
 396       ->setMemory();
 397     self::buildGroupContactTempTable([$groupID], $groupContactsTempTable);
 398     $tempTable = $groupContactsTempTable->getName();
 399
 400     // Don't call clearGroupContactCache as we don't want to clear the cache dates
 401     // The will get updated by updateCacheTime() below and not clearing the dates reduces
 402     // the chance that loadAll() will try and rebuild at the same time.
 403     $clearCacheQuery = "
 404     DELETE  g
 405       FROM  civicrm_group_contact_cache g
 406       WHERE  g.group_id = %1 ";
 407     $params = [
 408       1 => [$groupID, 'Integer'],
 409     ];
 410     CRM_Core_DAO::executeQuery($clearCacheQuery, $params);
 411
 412     CRM_Core_DAO::executeQuery(
 413       "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
 414         SELECT DISTINCT contact_id, group_id FROM $tempTable
 415       ");
 416     $groupContactsTempTable->drop();
 417     self::updateCacheTime([$groupID], TRUE);
 418
 419     $lock->release();
 420   }
 421
 422   /**
 423    * Retrieve the smart group cache timeout in minutes.
 424    *
 425    * This checks if a timeout has been configured. If one has then smart groups should not
 426    * be refreshed more frequently than the time out. If a group was recently refreshed it should not
 427    * refresh again within that period.
 428    *
 429    * @return int
 430    */
 431   public static function smartGroupCacheTimeout() {
 432     $config = CRM_Core_Config::singleton();
 433
 434     if (
 435       isset($config->smartGroupCacheTimeout) &&
 436       is_numeric($config->smartGroupCacheTimeout)
 437     ) {
 438       return $config->smartGroupCacheTimeout;
 439     }
 440
 441     // Default to 5 minutes.
 442     return 5;
 443   }
 444
 445   /**
 446    * Get all the smart groups that this contact belongs to.
 447    *
 448    * Note that this could potentially be a super slow function since
 449    * it ensure that all contact groups are loaded in the cache
 450    *
 451    * @param int $contactID
 452    *
 453    * @return array
 454    *   an array of groups that this contact belongs to
 455    */
 456   public static function contactGroup(int $contactID): array {
 457
 458     self::loadAll();
 459
 460     $sql = "
 461 SELECT     gc.group_id, gc.contact_id, g.title, g.children, g.description
 462 FROM       civicrm_group_contact_cache gc
 463 INNER JOIN civicrm_group g ON g.id = gc.group_id
 464 WHERE      gc.contact_id = $contactID
 465             AND (g.is_hidden = 0 OR g.is_hidden IS NULL)
 466 ORDER BY   gc.contact_id, g.children
 467 ";
 468
 469     $dao = CRM_Core_DAO::executeQuery($sql);
 470     $contactGroup = [];
 471     $prevContactID = NULL;
 472     while ($dao->fetch()) {
 473       if (
 474         $prevContactID &&
 475         $prevContactID != $dao->contact_id
 476       ) {
 477         $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 478       }
 479       $prevContactID = $dao->contact_id;
 480       if (!array_key_exists($dao->contact_id, $contactGroup)) {
 481         $contactGroup[$dao->contact_id]
 482           = ['group' => [], 'groupTitle' => []];
 483       }
 484
 485       $contactGroup[$dao->contact_id]['group'][]
 486         = [
 487           'id' => $dao->group_id,
 488           'title' => $dao->title,
 489           'description' => $dao->description,
 490           'children' => $dao->children,
 491         ];
 492       $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title;
 493     }
 494
 495     if ($prevContactID) {
 496       $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 497     }
 498
 499     if ((!empty($contactGroup[$contactID]))) {
 500       return $contactGroup[$contactID];
 501     }
 502     return $contactGroup;
 503   }
 504
 505   /**
 506    * Get the datetime from which the cache should be considered invalid.
 507    *
 508    * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was
 509    * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago.
 510    *
 511    * @return string
 512    */
 513   public static function getCacheInvalidDateTime(): string {
 514     return date('YmdHis', strtotime("-" . self::smartGroupCacheTimeout() . " Minutes"));
 515   }
 516
 517   /**
 518    * Invalidates the smart group cache for a particular group
 519    * @param int $groupID - Group to invalidate
 520    */
 521   public static function invalidateGroupContactCache($groupID): void {
 522     CRM_Core_DAO::executeQuery("UPDATE civicrm_group
 523       SET cache_date = NULL
 524       WHERE id = %1 AND (saved_search_id IS NOT NULL OR children IS NOT NULL)", [
 525         1 => [$groupID, 'Positive'],
 526       ]);
 527   }
 528
 529   /**
 530    * @param array $savedSearch
 531    * @param string $addSelect
 532    * @param string $excludeClause
 533    * @return string
 534    * @throws API_Exception
 535    * @throws \Civi\API\Exception\NotImplementedException
 536    * @throws CRM_Core_Exception
 537    */
 538   protected static function getApiSQL(array $savedSearch, string $addSelect, string $excludeClause) {
 539     $apiParams = $savedSearch['api_params'] + ['select' => ['id'], 'checkPermissions' => FALSE];
 540     $idField = SqlExpression::convert($apiParams['select'][0], TRUE)->getAlias();
 541     // Unless there's a HAVING clause, we don't care about other columns
 542     if (empty($apiParams['having'])) {
 543       $apiParams['select'] = array_slice($apiParams['select'], 0, 1);
 544     }
 545     /* @var $api \Civi\Api4\Generic\DAOGetAction */
 546     $api = Request::create($savedSearch['api_entity'], 'get', $apiParams);
 547     $query = new Api4SelectQuery($api);
 548     $query->forceSelectId = FALSE;
 549     $query->getQuery()->having("$idField $excludeClause");
 550     $sql = $query->getSql();
 551     // Place sql in a nested sub-query, otherwise HAVING is impossible on any field other than contact_id
 552     return "SELECT $addSelect, `$idField` AS contact_id FROM ($sql) api_query";
 553   }
 554
 555   /**
 556    * Get sql from a custom search.
 557    *
 558    * We split it up and store custom class
 559    * so temp tables are not destroyed if they are used
 560    *
 561    * @param int $savedSearchID
 562    * @param array $ssParams
 563    * @param string $addSelect
 564    * @param string $excludeClause
 565    *
 566    * @return string
 567    * @throws CRM_Core_Exception
 568    */
 569   protected static function getCustomSearchSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
 570     $searchSQL = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs();
 571     $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
 572     if (strpos($searchSQL, 'WHERE') === FALSE) {
 573       $searchSQL .= " WHERE contact_a.id $excludeClause";
 574     }
 575     else {
 576       $searchSQL .= " AND contact_a.id $excludeClause";
 577     }
 578     return preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $searchSQL);
 579   }
 580
 581   /**
 582    * Get array of sql from a saved query object group.
 583    *
 584    * @param int $savedSearchID
 585    * @param array $ssParams
 586    * @param string $addSelect
 587    * @param string $excludeClause
 588    *
 589    * @return string
 590    * @throws \CRM_Core_Exception
 591    * @throws \CiviCRM_API3_Exception
 592    */
 593   protected static function getQueryObjectSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
 594     $returnProperties = NULL;
 595     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
 596       $fv = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 597       $returnProperties = CRM_Core_BAO_Mapping::returnProperties($fv);
 598     }
 599     $formValues = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 600     // CRM-17075 using the formValues in this way imposes extra logic and complexity.
 601     // we have the where_clause and where tables stored in the saved_search table
 602     // and should use these rather than re-processing the form criteria (which over-works
 603     // the link between the form layer & the query layer too).
 604     // It's hard to think of when you would want to use anything other than return
 605     // properties = array('contact_id' => 1) here as the point would appear to be to
 606     // generate the list of contact ids in the group.
 607     // @todo review this to use values in saved_search table (preferably for 4.8).
 608     $query
 609       = new CRM_Contact_BAO_Query(
 610       $ssParams, $returnProperties, NULL,
 611       FALSE, FALSE, 1,
 612       TRUE, TRUE,
 613       FALSE,
 614       $formValues['display_relationship_type'] ?? NULL,
 615       $formValues['operator'] ?? 'AND'
 616     );
 617     $query->_useDistinct = FALSE;
 618     $query->_useGroupBy = FALSE;
 619     $sqlParts = $query->getSearchSQLParts(
 620       0, 0, NULL,
 621       FALSE, FALSE,
 622       FALSE, TRUE,
 623       "contact_a.id $excludeClause"
 624     );
 625     $select = preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $sqlParts['select']);
 626
 627     return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}";
 628   }
 629
 630   /**
 631    * Build a temporary table for the contacts in the specified group.
 632    *
 633    * @param array $groupIDs
 634    *   Currently only one id is build but this has been written
 635    *   to make it easy to switch to multiple.
 636    * @param CRM_Utils_SQL_TempTable $tempTableObject
 637    *
 638    * @throws \API_Exception
 639    * @throws \CRM_Core_Exception
 640    * @throws \CiviCRM_API3_Exception
 641    */
 642   protected static function buildGroupContactTempTable(array $groupIDs, $tempTableObject): void {
 643     $group = Group::get(FALSE)->addWhere('id', 'IN', $groupIDs)
 644       ->setSelect(['saved_search_id', 'children'])->execute()->first();
 645     $groupID = (int) $group['id'];
 646
 647     if ($group['saved_search_id']) {
 648       $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($group['saved_search_id']);
 649
 650       $excludeClause = "NOT IN (
 651                         SELECT contact_id FROM civicrm_group_contact
 652                         WHERE civicrm_group_contact.status = 'Removed'
 653                         AND civicrm_group_contact.group_id = $groupID )";
 654       $addSelect = "$groupID AS group_id";
 655
 656       if (!empty($ssParams['api_entity'])) {
 657         $sql = self::getApiSQL($ssParams, $addSelect, $excludeClause);
 658       }
 659       else {
 660         // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance
 661         if (!empty($ssParams)) {
 662           CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams);
 663         }
 664         if (isset($ssParams['customSearchID'])) {
 665           $sql = self::getCustomSearchSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause);
 666         }
 667         else {
 668           $sql = self::getQueryObjectSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause);
 669         }
 670       }
 671     }
 672
 673     $tempTableName = $tempTableObject->getName();
 674     $tempTableObject->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)');
 675
 676     if (!empty($sql)) {
 677       $contactQueries[] = $sql;
 678     }
 679     // lets also store the records that are explicitly added to the group
 680     // this allows us to skip the group contact LEFT JOIN
 681     $contactQueries[] =
 682       "SELECT $groupID as group_id, contact_id as contact_id
 683        FROM   civicrm_group_contact
 684        WHERE  civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID ";
 685
 686     self::clearGroupContactCache($groupID);
 687
 688     foreach ($contactQueries as $contactQuery) {
 689       CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) {$contactQuery}");
 690     }
 691
 692     CRM_Core_DAO::reenableFullGroupByMode();
 693
 694     if ($group['children']) {
 695
 696       // Store a list of contacts who are removed from the parent group
 697       $sqlContactsRemovedFromGroup = "
 698 SELECT contact_id
 699 FROM civicrm_group_contact
 700 WHERE  civicrm_group_contact.status = 'Removed'
 701 AND  civicrm_group_contact.group_id = $groupID ";
 702       $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup);
 703       $removed_contacts = [];
 704       while ($dao->fetch()) {
 705         $removed_contacts[] = $dao->contact_id;
 706       }
 707
 708       $childrenIDs = explode(',', $group['children']);
 709       foreach ($childrenIDs as $childID) {
 710         $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE);
 711         // Unset each contact that is removed from the parent group
 712         foreach ($removed_contacts as $removed_contact) {
 713           unset($contactIDs[$removed_contact]);
 714         }
 715         if (empty($contactIDs)) {
 716           // This child group has no contact IDs so we don't need to add them to
 717           continue;
 718         }
 719         $values = [];
 720         foreach ($contactIDs as $contactID => $dontCare) {
 721           $values[] = "({$groupID},{$contactID})";
 722         }
 723         $str = implode(',', $values);
 724         CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) VALUES $str");
 725       }
 726     }
 727   }
 728
 729   /**
 730    * Populate a temporary table with group ids and contact ids.
 731    *
 732    * Do not call this outside of core tested code - it WILL change.
 733    *
 734    * @param array[int] $groupIDs
 735    * @param string $temporaryTable
 736    *
 737    * @throws \CiviCRM_API3_Exception
 738    */
 739   public static function populateTemporaryTableWithContactsInGroups(array $groupIDs, string $temporaryTable): void {
 740     $groups = civicrm_api3('Group', 'get', [
 741       'is_active' => 1,
 742       'id' => ['IN' => $groupIDs],
 743       'saved_search_id' => ['>' => 0],
 744       'return' => 'id',
 745     ]);
 746     $smartGroups = array_keys($groups['values']);
 747
 748     $query = "
 749        SELECT DISTINCT group_contact.contact_id as contact_id
 750        FROM civicrm_group_contact group_contact
 751        WHERE group_contact.group_id IN (" . implode(', ', $groupIDs) . ")
 752        AND group_contact.status = 'Added' ";
 753
 754     if (!empty($smartGroups)) {
 755       CRM_Contact_BAO_GroupContactCache::check($smartGroups);
 756       $smartGroups = implode(',', $smartGroups);
 757       $query .= "
 758         UNION DISTINCT
 759         SELECT smartgroup_contact.contact_id as contact_id
 760         FROM civicrm_group_contact_cache smartgroup_contact
 761         WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
 762     }
 763     CRM_Core_DAO::executeQuery('INSERT INTO ' . $temporaryTable . ' ' . $query);
 764   }
 765
 766   /**
 767    * @param array|null $groupIDs
 768    * @param int $limit
 769    *
 770    * @return array
 771    */
 772   protected static function getGroupsNeedingRefreshing(?array $groupIDs, int $limit): array {
 773     $groupIDClause = NULL;
 774     // ensure that all the smart groups are loaded
 775     // this function is expensive and should be sparingly used if groupIDs is empty
 776     if (!empty($groupIDs)) {
 777       // note escapeString is a must here and we can't send the imploded value as second argument to
 778       // the executeQuery(), since that would put single quote around the string and such a string
 779       // of comma separated integers would not work.
 780       $groupIDString = CRM_Core_DAO::escapeString(implode(', ', $groupIDs));
 781       $groupIDClause = "g.id IN ({$groupIDString})";
 782     }
 783
 784     $query = self::groupRefreshedClause($groupIDClause);
 785
 786     $limitClause = $orderClause = NULL;
 787     if ($limit > 0) {
 788       $limitClause = " LIMIT 0, $limit";
 789       $orderClause = " ORDER BY g.cache_date";
 790     }
 791     // We ignore hidden groups and disabled groups
 792     $query .= "
 793         $orderClause
 794         $limitClause
 795 ";
 796
 797     $dao = CRM_Core_DAO::executeQuery($query);
 798     $processGroupIDs = [];
 799     while ($dao->fetch()) {
 800       $processGroupIDs[] = $dao->id;
 801     }
 802     return $processGroupIDs;
 803   }
 804
 805 }