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