3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Contact_BAO_GroupContactCache
extends CRM_Contact_DAO_GroupContactCache
{
19 public static $_alreadyLoaded = [];
22 * Get a list of caching modes.
26 public static function getModes() {
28 // Flush expired caches in response to user actions.
29 'opportunistic' => ts('Opportunistic Flush'),
31 // Flush expired caches via background cron jobs.
32 'deterministic' => ts('Cron Flush'),
37 * Check to see if we have cache entries for this group.
39 * If not, regenerate, else return.
41 * @param array $groupIDs
42 * Of group that we are checking against.
45 * TRUE if we did not regenerate, FALSE if we did
47 public static function check($groupIDs) {
48 if (empty($groupIDs)) {
52 return self
::loadAll($groupIDs);
56 * Formulate the query to see which groups needs to be refreshed.
58 * The calculation is based on their cache date and the smartGroupCacheTimeOut
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.
66 * the sql query which lists the groups that need to be refreshed
68 public static function groupRefreshedClause($groupIDClause = NULL, $includeHiddenGroups = FALSE) {
69 $smartGroupCacheTimeoutDateTime = self
::getCacheInvalidDateTime();
74 WHERE ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL )
78 OR cache_date <= $smartGroupCacheTimeoutDateTime
79 OR NOW() >= g.refresh_date
82 if (!$includeHiddenGroups) {
83 $query .= "AND (g.is_hidden = 0 OR g.is_hidden IS NULL)";
86 if (!empty($groupIDClause)) {
87 $query .= " AND ( $groupIDClause ) ";
94 * Check to see if a group has been refreshed recently.
96 * This is primarily used in a locking scenario when some other process might have refreshed things underneath
101 * @param bool $includeHiddenGroups
102 * Hidden groups are excluded by default.
105 * the sql query which lists the groups that need to be refreshed
107 public static function shouldGroupBeRefreshed($groupID, $includeHiddenGroups = FALSE) {
108 $query = self
::groupRefreshedClause("g.id = %1", $includeHiddenGroups);
109 $params = [1 => [$groupID, 'Integer']];
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);
116 * Check to see if we have cache entries for this group.
118 * if not, regenerate, else return
120 * @param int|array $groupIDs groupIDs of group that we are checking against
121 * if empty, all groups are checked
123 * Limits the number of groups we evaluate.
126 * TRUE if we did not regenerate, FALSE if we did
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;
136 if (!is_array($groupIDs)) {
137 $groupIDs = [$groupIDs];
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));
145 $groupIDClause = "g.id IN ({$groupIDString})";
148 $query = self
::groupRefreshedClause($groupIDClause);
150 $limitClause = $orderClause = NULL;
152 $limitClause = " LIMIT 0, $limit";
153 $orderClause = " ORDER BY g.cache_date, g.refresh_date";
155 // We ignore hidden groups and disabled groups
161 $dao = CRM_Core_DAO
::executeQuery($query);
162 $processGroupIDs = [];
163 $refreshGroupIDs = $groupIDs;
164 while ($dao->fetch()) {
165 $processGroupIDs[] = $dao->id
;
167 // remove this id from refreshGroupIDs
168 foreach ($refreshGroupIDs as $idx => $gid) {
169 if ($gid == $dao->id
) {
170 unset($refreshGroupIDs[$idx]);
176 if (!empty($refreshGroupIDs)) {
177 $refreshGroupIDString = CRM_Core_DAO
::escapeString(implode(', ', $refreshGroupIDs));
178 $time = self
::getRefreshDateTime();
180 UPDATE civicrm_group g
181 SET g.refresh_date = $time
182 WHERE g.id IN ( {$refreshGroupIDString} )
183 AND g.refresh_date IS NULL
185 CRM_Core_DAO
::executeQuery($query);
188 if (empty($processGroupIDs)) {
192 self
::add($processGroupIDs);
198 * Build the smart group cache for given groups.
200 * @param array $groupIDs
202 public static function add($groupIDs) {
203 $groupIDs = (array) $groupIDs;
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);
215 * Store values into the group contact cache.
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.
220 * @param array $groupID
221 * @param array $values
223 public static function store($groupID, &$values) {
226 // sort the values so we put group IDs in front and hence optimize
227 // mysql storage (or so we think) CRM-9493
230 // to avoid long strings, lets do BULK_INSERT_COUNT values at a time
231 while (!empty($values)) {
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);
238 self
::updateCacheTime($groupID, $processed);
242 * Change the cache_date.
244 * @param array $groupID
245 * @param bool $processed
246 * Whether the cache data was recently modified.
248 public static function updateCacheTime($groupID, $processed) {
249 // only update cache entry if we had any values
251 // also update the group with cache date information
252 $now = date('YmdHis');
260 $groupIDs = implode(',', $groupID);
263 SET cache_date = $now, refresh_date = $refresh
264 WHERE id IN ( $groupIDs )
266 CRM_Core_DAO
::executeQuery($sql);
270 * Function to clear group contact cache and reset the corresponding
271 * group's cache and refresh date
273 * @param int $groupID
276 public static function clearGroupContactCache($groupID) {
277 $transaction = new CRM_Core_Transaction();
280 FROM civicrm_group_contact_cache g
281 WHERE g.group_id = %1 ";
284 UPDATE civicrm_group g
285 SET cache_date = null, refresh_date = null
289 1 => [$groupID, 'Integer'],
292 CRM_Core_DAO
::executeQuery($query, $params);
293 // also update the cache_date for these groups
294 CRM_Core_DAO
::executeQuery($update, $params);
295 unset(self
::$_alreadyLoaded[$groupID]);
297 $transaction->commit();
301 * Refresh the smart group cache tables.
303 * This involves clearing out any aged entries (based on the site timeout setting) and resetting the time outs.
305 * This function should be called via the opportunistic or deterministic cache refresh function to make the intent
308 protected static function flushCaches() {
310 $lock = self
::getLockForRefresh();
312 catch (CRM_Core_Exception
$e) {
313 // Someone else is kindly doing the refresh for us right now.
316 $params = [1 => [self
::getCacheInvalidDateTime(), 'String']];
317 // @todo this is consistent with previous behaviour but as the first query could take several seconds the second
318 // could become inaccurate. It seems to make more sense to fetch them first & delete from an array (which would
319 // also reduce joins). If we do this we should also consider how best to iterate the groups. If we do them one at
320 // a time we could call a hook, allowing people to manage the frequency on their groups, or possibly custom searches
321 // might do that too. However, for 2000 groups that's 2000 iterations. If we do all once we potentially create a
322 // slow query. It's worth noting the speed issue generally relates to the size of the group but if one slow group
323 // is in a query with 500 fast ones all 500 get locked. One approach might be to calculate group size or the
324 // number of groups & then process all at once or many query runs depending on what is found. Of course those
325 // preliminary queries would need speed testing.
326 CRM_Core_DAO
::executeQuery(
329 FROM civicrm_group_contact_cache gc
330 INNER JOIN civicrm_group g ON g.id = gc.group_id
331 WHERE g.cache_date <= %1
336 // Clear these out without resetting them because we are not building caches here, only clearing them,
337 // so the state is 'as if they had never been built'.
338 CRM_Core_DAO
::executeQuery(
340 UPDATE civicrm_group g
341 SET cache_date = NULL,
343 WHERE g.cache_date <= %1
351 * Check if the refresh is already initiated.
353 * We have 2 imperfect methods for this:
354 * 1) a static variable in the function. This works fine within a request
355 * 2) a mysql lock. This works fine as long as CiviMail is not running, or if mysql is version 5.7+
357 * Where these 2 locks fail we get 2 processes running at the same time, but we have at least minimised that.
359 * @return \Civi\Core\Lock\LockInterface
360 * @throws \CRM_Core_Exception
362 protected static function getLockForRefresh() {
363 if (!isset(Civi
::$statics[__CLASS__
]['is_refresh_init'])) {
364 Civi
::$statics[__CLASS__
] = ['is_refresh_init' => FALSE];
367 if (Civi
::$statics[__CLASS__
]['is_refresh_init']) {
368 throw new CRM_Core_Exception('A refresh has already run in this process');
370 $lock = Civi
::lockManager()->acquire('data.core.group.refresh');
371 if ($lock->isAcquired()) {
372 Civi
::$statics[__CLASS__
]['is_refresh_init'] = TRUE;
375 throw new CRM_Core_Exception('Mysql lock unavailable');
379 * Do an opportunistic cache refresh if the site is configured for these.
381 * Sites that do not run the smart group clearing cron job should refresh the
382 * caches on demand. The user session will be forced to wait so it is less
385 public static function opportunisticCacheFlush() {
386 if (Civi
::settings()->get('smart_group_cache_refresh_mode') == 'opportunistic') {
392 * Do a forced cache refresh.
394 * This function is appropriate to be called by system jobs & non-user sessions.
396 public static function deterministicCacheFlush() {
397 if (self
::smartGroupCacheTimeout() == 0) {
398 CRM_Core_DAO
::executeQuery("TRUNCATE civicrm_group_contact_cache");
399 CRM_Core_DAO
::executeQuery("
400 UPDATE civicrm_group g
401 SET cache_date = null, refresh_date = null");
409 * Remove one or more contacts from the smart group cache.
411 * @param int|array $cid
412 * @param int $groupId
415 * TRUE if successful.
417 public static function removeContact($cid, $groupId = NULL) {
420 foreach ((array) $cid as $c) {
421 $cids[] = CRM_Utils_Type
::escape($c, 'Integer');
424 $condition = count($cids) == 1 ?
"= {$cids[0]}" : "IN (" . implode(',', $cids) . ")";
426 $condition .= " AND group_id = " . CRM_Utils_Type
::escape($groupId, 'Integer');
428 $sql = "DELETE FROM civicrm_group_contact_cache WHERE contact_id $condition";
429 CRM_Core_DAO
::executeQuery($sql);
436 * Load the smart group cache for a saved search.
438 * @param object $group
439 * The smart group that needs to be loaded.
441 * Should we force a search through.
443 * @throws \CRM_Core_Exception
445 public static function load(&$group, $force = FALSE) {
446 $groupID = $group->id
;
447 $savedSearchID = $group->saved_search_id
;
448 if (array_key_exists($groupID, self
::$_alreadyLoaded) && !$force) {
452 self
::$_alreadyLoaded[$groupID] = 1;
454 // FIXME: some other process could have actually done the work before we got here,
455 // Ensure that work needs to be done before continuing
456 if (!$force && !self
::shouldGroupBeRefreshed($groupID, TRUE)) {
462 if ($savedSearchID) {
463 $ssParams = CRM_Contact_BAO_SavedSearch
::getSearchParams($savedSearchID);
465 if (!empty($ssParams['api_entity'])) {
467 $sql = self
::getApiSQL($savedSearchID, $ssParams);
470 $mainCol = 'contact_a';
471 // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance
472 if (!empty($ssParams)) {
473 CRM_Contact_BAO_ProximityQuery
::fixInputParams($ssParams);
475 if (isset($ssParams['customSearchID'])) {
476 $sql = self
::getCustomSearchSQL($savedSearchID, $ssParams);
479 $sql = self
::getQueryObjectSQL($savedSearchID, $ssParams);
482 $groupID = CRM_Utils_Type
::escape($groupID, 'Integer');
483 $sql['from'] .= " AND $mainCol.id NOT IN (
484 SELECT contact_id FROM civicrm_group_contact
485 WHERE civicrm_group_contact.status = 'Removed'
486 AND civicrm_group_contact.group_id = $groupID ) ";
489 if (!empty($sql['select'])) {
490 $sql['select'] = preg_replace("/^\s*SELECT/", "SELECT $groupID as group_id, ", $sql['select']);
493 $groupContactsTempTable = CRM_Utils_SQL_TempTable
::build()->setCategory('gccache')->setMemory();
494 $tempTable = $groupContactsTempTable->getName();
495 $groupContactsTempTable->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)');
497 $contactQueries[] = $sql;
498 // lets also store the records that are explicitly added to the group
499 // this allows us to skip the group contact LEFT JOIN
500 $contactQueries[] = [
501 'select' => "SELECT $groupID as group_id, contact_id as contact_id",
502 'from' => " FROM civicrm_group_contact WHERE civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID ",
505 self
::clearGroupContactCache($groupID);
507 foreach ($contactQueries as $contactQuery) {
508 if (empty($contactQuery['select']) ||
empty($contactQuery['from'])) {
511 if (CRM_Core_DAO
::singleValueQuery("SELECT COUNT(*) {$contactQuery['from']}") > 0) {
512 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) {$contactQuery['select']} {$contactQuery['from']}");
516 if ($group->children
) {
518 // Store a list of contacts who are removed from the parent group
519 $sqlContactsRemovedFromGroup = "
521 FROM civicrm_group_contact
522 WHERE civicrm_group_contact.status = 'Removed'
523 AND civicrm_group_contact.group_id = $groupID ";
524 $dao = CRM_Core_DAO
::executeQuery($sqlContactsRemovedFromGroup);
525 $removed_contacts = [];
526 while ($dao->fetch()) {
527 $removed_contacts[] = $dao->contact_id
;
530 $childrenIDs = explode(',', $group->children
);
531 foreach ($childrenIDs as $childID) {
532 $contactIDs = CRM_Contact_BAO_Group
::getMember($childID, FALSE);
533 // Unset each contact that is removed from the parent group
534 foreach ($removed_contacts as $removed_contact) {
535 unset($contactIDs[$removed_contact]);
537 if (empty($contactIDs)) {
538 // This child group has no contact IDs so we don't need to add them to
542 foreach ($contactIDs as $contactID => $dontCare) {
543 $values[] = "({$groupID},{$contactID})";
545 $str = implode(',', $values);
546 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) VALUES $str");
550 // grab a lock so other processes don't compete and do the same query
551 $lock = Civi
::lockManager()->acquire("data.core.group.{$groupID}");
552 if (!$lock->isAcquired()) {
553 // this can cause inconsistent results since we don't know if the other process
554 // will fill up the cache before our calling routine needs it.
555 // however this routine does not return the status either, so basically
556 // its a "lets return and hope for the best"
560 // Don't call clearGroupContactCache as we don't want to clear the cache dates
561 // The will get updated by updateCacheTime() below and not clearing the dates reduces
562 // the chance that loadAll() will try and rebuild at the same time.
565 FROM civicrm_group_contact_cache g
566 WHERE g.group_id = %1 ";
568 1 => [$groupID, 'Integer'],
570 CRM_Core_DAO
::executeQuery($clearCacheQuery, $params);
572 CRM_Core_DAO
::executeQuery(
573 "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
574 SELECT DISTINCT contact_id, group_id FROM $tempTable
576 $groupContactsTempTable->drop();
577 self
::updateCacheTime([$groupID], TRUE);
583 * Retrieve the smart group cache timeout in minutes.
585 * This checks if a timeout has been configured. If one has then smart groups should not
586 * be refreshed more frequently than the time out. If a group was recently refreshed it should not
587 * refresh again within that period.
591 public static function smartGroupCacheTimeout() {
592 $config = CRM_Core_Config
::singleton();
595 isset($config->smartGroupCacheTimeout
) &&
596 is_numeric($config->smartGroupCacheTimeout
)
598 return $config->smartGroupCacheTimeout
;
601 // Default to 5 minutes.
606 * Get all the smart groups that this contact belongs to.
608 * Note that this could potentially be a super slow function since
609 * it ensure that all contact groups are loaded in the cache
611 * @param int $contactID
612 * @param bool $showHidden
613 * Hidden groups are shown only if this flag is set.
616 * an array of groups that this contact belongs to
618 public static function contactGroup($contactID, $showHidden = FALSE) {
619 if (empty($contactID)) {
623 if (is_array($contactID)) {
624 $contactIDs = $contactID;
627 $contactIDs = [$contactID];
634 $hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) ';
637 $contactIDString = CRM_Core_DAO
::escapeString(implode(', ', $contactIDs));
639 SELECT gc.group_id, gc.contact_id, g.title, g.children, g.description
640 FROM civicrm_group_contact_cache gc
641 INNER JOIN civicrm_group g ON g.id = gc.group_id
642 WHERE gc.contact_id IN ($contactIDString)
644 ORDER BY gc.contact_id, g.children
647 $dao = CRM_Core_DAO
::executeQuery($sql);
649 $prevContactID = NULL;
650 while ($dao->fetch()) {
653 $prevContactID != $dao->contact_id
655 $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
657 $prevContactID = $dao->contact_id
;
658 if (!array_key_exists($dao->contact_id
, $contactGroup)) {
659 $contactGroup[$dao->contact_id
]
660 = ['group' => [], 'groupTitle' => []];
663 $contactGroup[$dao->contact_id
]['group'][]
665 'id' => $dao->group_id
,
666 'title' => $dao->title
,
667 'description' => $dao->description
,
668 'children' => $dao->children
,
670 $contactGroup[$dao->contact_id
]['groupTitle'][] = $dao->title
;
673 if ($prevContactID) {
674 $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
677 if ((!empty($contactGroup[$contactID]) && is_numeric($contactID))) {
678 return $contactGroup[$contactID];
681 return $contactGroup;
686 * Get the datetime from which the cache should be considered invalid.
688 * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was
689 * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago.
693 public static function getCacheInvalidDateTime() {
694 return date('YmdHis', strtotime("-" . self
::smartGroupCacheTimeout() . " Minutes"));
698 * Get the date when the cache should be refreshed from.
700 * Ie. now + the offset & we will delete anything prior to then.
704 public static function getRefreshDateTime() {
705 return date('YmdHis', strtotime("+ " . self
::smartGroupCacheTimeout() . " Minutes"));
709 * Invalidates the smart group cache for a particular group
710 * @param int $groupID - Group to invalidate
712 public static function invalidateGroupContactCache($groupID) {
713 CRM_Core_DAO
::executeQuery("UPDATE civicrm_group
714 SET cache_date = NULL, refresh_date = NULL
716 1 => [$groupID, 'Positive'],
721 * @param $savedSearchID
722 * @param array $savedSearch
724 * @throws API_Exception
725 * @throws \Civi\API\Exception\NotImplementedException
726 * @throws CRM_Core_Exception
728 protected static function getApiSQL($savedSearchID, array $savedSearch): array {
729 $api = \Civi\API\Request
::create($savedSearch['api_entity'], 'get', $savedSearch['api_params']);
730 $query = new \Civi\Api4\Query\
Api4SelectQuery($api->getEntityName(), FALSE, $api->entityFields());
731 $query->select
= ['id'];
732 $query->where
= $api->getWhere();
733 $query->orderBy
= $api->getOrderBy();
734 $query->limit
= $api->getLimit();
735 $query->offset
= $api->getOffset();
736 $sql = $query->getSql();
738 'select' => substr($sql, 0, strpos($sql, 'FROM')),
739 'from' => substr($sql, strpos($sql, 'FROM')),
744 * Get sql from a custom search.
746 * @param int $savedSearchID
747 * @param array $ssParams
752 protected static function getCustomSearchSQL($savedSearchID, array $ssParams): array {
755 // we split it up and store custom class
756 // so temp tables are not destroyed if they are used
757 // hence customClass is defined above at top of function
758 $customClass = CRM_Contact_BAO_SearchCustom
::customClass($ssParams['customSearchID'], $savedSearchID);
759 $searchSQL = $customClass->contactIDs();
760 $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
761 if (strpos($searchSQL, 'WHERE') === FALSE) {
762 $searchSQL .= " WHERE ( 1 ) ";
765 'select' => substr($searchSQL, 0, strpos($searchSQL, 'FROM')),
766 'from' => substr($searchSQL, strpos($searchSQL, 'FROM')),
772 * Get array of sql from a saved query object group.
774 * @param int $savedSearchID
775 * @param array $ssParams
778 * @throws \CRM_Core_Exception
779 * @throws \CiviCRM_API3_Exception
781 protected static function getQueryObjectSQL($savedSearchID, array $ssParams): array {
782 $returnProperties = NULL;
783 if (CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
784 $fv = CRM_Contact_BAO_SavedSearch
::getFormValues($savedSearchID);
785 $returnProperties = CRM_Core_BAO_Mapping
::returnProperties($fv);
787 $formValues = CRM_Contact_BAO_SavedSearch
::getFormValues($savedSearchID);
788 // CRM-17075 using the formValues in this way imposes extra logic and complexity.
789 // we have the where_clause and where tables stored in the saved_search table
790 // and should use these rather than re-processing the form criteria (which over-works
791 // the link between the form layer & the query layer too).
792 // It's hard to think of when you would want to use anything other than return
793 // properties = array('contact_id' => 1) here as the point would appear to be to
794 // generate the list of contact ids in the group.
795 // @todo review this to use values in saved_search table (preferably for 4.8).
797 = new CRM_Contact_BAO_Query(
798 $ssParams, $returnProperties, NULL,
802 CRM_Utils_Array
::value('display_relationship_type', $formValues),
803 CRM_Utils_Array
::value('operator', $formValues, 'AND')
805 $query->_useDistinct
= FALSE;
806 $query->_useGroupBy
= FALSE;
807 $sqlParts = $query->getSearchSQLParts(
813 'select' => $sqlParts['select'],
814 'from' => "{$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']}",