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