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 +--------------------------------------------------------------------+
12 use Civi\Api4\Query\SqlExpression
;
17 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 class CRM_Contact_BAO_GroupContactCache
extends CRM_Contact_DAO_GroupContactCache
{
21 public static $_alreadyLoaded = [];
24 * Get a list of caching modes.
28 public static function getModes() {
30 // Flush expired caches in response to user actions.
31 'opportunistic' => ts('Opportunistic Flush'),
33 // Flush expired caches via background cron jobs.
34 'deterministic' => ts('Cron Flush'),
39 * Check to see if we have cache entries for this group.
41 * If not, regenerate, else return.
43 * @param array $groupIDs
44 * Of group that we are checking against.
47 * TRUE if we did not regenerate, FALSE if we did
49 public static function check($groupIDs) {
50 if (empty($groupIDs)) {
54 return self
::loadAll($groupIDs);
58 * Formulate the query to see which groups needs to be refreshed.
60 * The calculation is based on their cache date and the smartGroupCacheTimeOut
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.
68 * the sql query which lists the groups that need to be refreshed
70 public static function groupRefreshedClause($groupIDClause = NULL, $includeHiddenGroups = FALSE) {
71 $smartGroupCacheTimeoutDateTime = self
::getCacheInvalidDateTime();
76 WHERE ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL )
80 OR cache_date <= $smartGroupCacheTimeoutDateTime
81 OR NOW() >= g.refresh_date
84 if (!$includeHiddenGroups) {
85 $query .= "AND (g.is_hidden = 0 OR g.is_hidden IS NULL)";
88 if (!empty($groupIDClause)) {
89 $query .= " AND ( $groupIDClause ) ";
96 * Check to see if a group has been refreshed recently.
98 * This is primarily used in a locking scenario when some other process might have refreshed things underneath
101 * @param int $groupID
103 * @param bool $includeHiddenGroups
104 * Hidden groups are excluded by default.
107 * the sql query which lists the groups that need to be refreshed
109 public static function shouldGroupBeRefreshed($groupID, $includeHiddenGroups = FALSE) {
110 $query = self
::groupRefreshedClause("g.id = %1", $includeHiddenGroups);
111 $params = [1 => [$groupID, 'Integer']];
113 // if the query returns the group ID, it means the group is a valid candidate for refreshing
114 return CRM_Core_DAO
::singleValueQuery($query, $params);
118 * Check to see if we have cache entries for this group.
120 * if not, regenerate, else return
122 * @param int|array $groupIDs groupIDs of group that we are checking against
123 * if empty, all groups are checked
125 * Limits the number of groups we evaluate.
128 * TRUE if we did not regenerate, FALSE if we did
130 public static function loadAll($groupIDs = NULL, $limit = 0) {
131 // ensure that all the smart groups are loaded
132 // this function is expensive and should be sparingly used if groupIDs is empty
133 if (empty($groupIDs)) {
134 $groupIDClause = NULL;
138 if (!is_array($groupIDs)) {
139 $groupIDs = [$groupIDs];
142 // note escapeString is a must here and we can't send the imploded value as second argument to
143 // the executeQuery(), since that would put single quote around the string and such a string
144 // of comma separated integers would not work.
145 $groupIDString = CRM_Core_DAO
::escapeString(implode(', ', $groupIDs));
147 $groupIDClause = "g.id IN ({$groupIDString})";
150 $query = self
::groupRefreshedClause($groupIDClause);
152 $limitClause = $orderClause = NULL;
154 $limitClause = " LIMIT 0, $limit";
155 $orderClause = " ORDER BY g.cache_date, g.refresh_date";
157 // We ignore hidden groups and disabled groups
163 $dao = CRM_Core_DAO
::executeQuery($query);
164 $processGroupIDs = [];
165 $refreshGroupIDs = $groupIDs;
166 while ($dao->fetch()) {
167 $processGroupIDs[] = $dao->id
;
169 // remove this id from refreshGroupIDs
170 foreach ($refreshGroupIDs as $idx => $gid) {
171 if ($gid == $dao->id
) {
172 unset($refreshGroupIDs[$idx]);
178 if (!empty($refreshGroupIDs)) {
179 $refreshGroupIDString = CRM_Core_DAO
::escapeString(implode(', ', $refreshGroupIDs));
180 $time = self
::getRefreshDateTime();
182 UPDATE civicrm_group g
183 SET g.refresh_date = $time
184 WHERE g.id IN ( {$refreshGroupIDString} )
185 AND g.refresh_date IS NULL
187 CRM_Core_DAO
::executeQuery($query);
190 if (empty($processGroupIDs)) {
194 self
::add($processGroupIDs);
200 * Build the smart group cache for given groups.
202 * @param array $groupIDs
204 public static function add($groupIDs) {
205 $groupIDs = (array) $groupIDs;
207 foreach ($groupIDs as $groupID) {
208 // first delete the current cache
209 self
::clearGroupContactCache($groupID);
210 $params = [['group', 'IN', [$groupID], 0, 0]];
211 // the below call updates the cache table as a byproduct of the query
212 CRM_Contact_BAO_Query
::apiQuery($params, ['contact_id'], NULL, NULL, 0, 0, FALSE);
217 * Store values into the group contact cache.
219 * @todo review use of INSERT IGNORE. This function appears to be slower that inserting
220 * with a left join. Also, 200 at once seems too little.
222 * @param array $groupID
223 * @param array $values
225 public static function store($groupID, &$values) {
228 // sort the values so we put group IDs in front and hence optimize
229 // mysql storage (or so we think) CRM-9493
232 // to avoid long strings, lets do BULK_INSERT_COUNT values at a time
233 while (!empty($values)) {
235 $input = array_splice($values, 0, CRM_Core_DAO
::BULK_INSERT_COUNT
);
236 $str = implode(',', $input);
237 $sql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES $str;";
238 CRM_Core_DAO
::executeQuery($sql);
240 self
::updateCacheTime($groupID, $processed);
244 * Change the cache_date.
246 * @param array $groupID
247 * @param bool $processed
248 * Whether the cache data was recently modified.
250 public static function updateCacheTime($groupID, $processed) {
251 // only update cache entry if we had any values
253 // also update the group with cache date information
254 $now = date('YmdHis');
262 $groupIDs = implode(',', $groupID);
265 SET cache_date = $now, refresh_date = $refresh
266 WHERE id IN ( $groupIDs )
268 CRM_Core_DAO
::executeQuery($sql);
272 * Function to clear group contact cache and reset the corresponding
273 * group's cache and refresh date
275 * @param int $groupID
278 public static function clearGroupContactCache($groupID) {
279 $transaction = new CRM_Core_Transaction();
282 FROM civicrm_group_contact_cache g
283 WHERE g.group_id = %1 ";
286 UPDATE civicrm_group g
287 SET cache_date = null, refresh_date = null
291 1 => [$groupID, 'Integer'],
294 CRM_Core_DAO
::executeQuery($query, $params);
295 // also update the cache_date for these groups
296 CRM_Core_DAO
::executeQuery($update, $params);
297 unset(self
::$_alreadyLoaded[$groupID]);
299 $transaction->commit();
303 * Refresh the smart group cache tables.
305 * This involves clearing out any aged entries (based on the site timeout setting) and resetting the time outs.
307 * This function should be called via the opportunistic or deterministic cache refresh function to make the intent
310 protected static function flushCaches() {
312 $lock = self
::getLockForRefresh();
314 catch (CRM_Core_Exception
$e) {
315 // Someone else is kindly doing the refresh for us right now.
318 $params = [1 => [self
::getCacheInvalidDateTime(), 'String']];
319 $groupsDAO = CRM_Core_DAO
::executeQuery("SELECT id FROM civicrm_group WHERE cache_date <= %1", $params);
321 while ($groupsDAO->fetch()) {
322 $expiredGroups[] = $groupsDAO->id
;
324 if (!empty($expiredGroups)) {
325 $expiredGroups = implode(',', $expiredGroups);
326 CRM_Core_DAO
::executeQuery("DELETE FROM civicrm_group_contact_cache WHERE group_id IN ({$expiredGroups})");
328 // Clear these out without resetting them because we are not building caches here, only clearing them,
329 // so the state is 'as if they had never been built'.
330 CRM_Core_DAO
::executeQuery("UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL WHERE id IN ({$expiredGroups})");
336 * Check if the refresh is already initiated.
338 * We have 2 imperfect methods for this:
339 * 1) a static variable in the function. This works fine within a request
340 * 2) a mysql lock. This works fine as long as CiviMail is not running, or if mysql is version 5.7+
342 * Where these 2 locks fail we get 2 processes running at the same time, but we have at least minimised that.
344 * @return \Civi\Core\Lock\LockInterface
345 * @throws \CRM_Core_Exception
347 protected static function getLockForRefresh() {
348 if (!isset(Civi
::$statics[__CLASS__
]['is_refresh_init'])) {
349 Civi
::$statics[__CLASS__
] = ['is_refresh_init' => FALSE];
352 if (Civi
::$statics[__CLASS__
]['is_refresh_init']) {
353 throw new CRM_Core_Exception('A refresh has already run in this process');
355 $lock = Civi
::lockManager()->acquire('data.core.group.refresh');
356 if ($lock->isAcquired()) {
357 Civi
::$statics[__CLASS__
]['is_refresh_init'] = TRUE;
360 throw new CRM_Core_Exception('Mysql lock unavailable');
364 * Do an opportunistic cache refresh if the site is configured for these.
366 * Sites that do not run the smart group clearing cron job should refresh the
367 * caches on demand. The user session will be forced to wait so it is less
370 public static function opportunisticCacheFlush() {
371 if (Civi
::settings()->get('smart_group_cache_refresh_mode') == 'opportunistic') {
377 * Do a forced cache refresh.
379 * This function is appropriate to be called by system jobs & non-user sessions.
381 public static function deterministicCacheFlush() {
382 if (self
::smartGroupCacheTimeout() == 0) {
383 CRM_Core_DAO
::executeQuery("TRUNCATE civicrm_group_contact_cache");
384 CRM_Core_DAO
::executeQuery("UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL");
392 * Remove one or more contacts from the smart group cache.
394 * @param int|array $cid
395 * @param int $groupId
398 * TRUE if successful.
400 public static function removeContact($cid, $groupId = NULL) {
403 foreach ((array) $cid as $c) {
404 $cids[] = CRM_Utils_Type
::escape($c, 'Integer');
407 $condition = count($cids) == 1 ?
"= {$cids[0]}" : "IN (" . implode(',', $cids) . ")";
409 $condition .= " AND group_id = " . CRM_Utils_Type
::escape($groupId, 'Integer');
411 $sql = "DELETE FROM civicrm_group_contact_cache WHERE contact_id $condition";
412 CRM_Core_DAO
::executeQuery($sql);
419 * Load the smart group cache for a saved search.
421 * @param object $group
422 * The smart group that needs to be loaded.
424 * Should we force a search through.
426 * @throws \CRM_Core_Exception
428 public static function load(&$group, $force = FALSE) {
429 $groupID = $group->id
;
430 $savedSearchID = $group->saved_search_id
;
431 if (array_key_exists($groupID, self
::$_alreadyLoaded) && !$force) {
435 self
::$_alreadyLoaded[$groupID] = 1;
437 // FIXME: some other process could have actually done the work before we got here,
438 // Ensure that work needs to be done before continuing
439 if (!$force && !self
::shouldGroupBeRefreshed($groupID, TRUE)) {
444 if ($savedSearchID) {
445 $ssParams = CRM_Contact_BAO_SavedSearch
::getSearchParams($savedSearchID);
446 $groupID = CRM_Utils_Type
::escape($groupID, 'Integer');
448 $excludeClause = "NOT IN (
449 SELECT contact_id FROM civicrm_group_contact
450 WHERE civicrm_group_contact.status = 'Removed'
451 AND civicrm_group_contact.group_id = $groupID )";
452 $addSelect = "$groupID AS group_id";
454 if (!empty($ssParams['api_entity'])) {
455 $sql = self
::getApiSQL($ssParams, $addSelect, $excludeClause);
458 // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance
459 if (!empty($ssParams)) {
460 CRM_Contact_BAO_ProximityQuery
::fixInputParams($ssParams);
462 if (isset($ssParams['customSearchID'])) {
463 $sql = self
::getCustomSearchSQL($savedSearchID, $ssParams, $addSelect, $excludeClause);
466 $sql = self
::getQueryObjectSQL($savedSearchID, $ssParams, $addSelect, $excludeClause);
471 $groupContactsTempTable = CRM_Utils_SQL_TempTable
::build()->setCategory('gccache')->setMemory();
472 $tempTable = $groupContactsTempTable->getName();
473 $groupContactsTempTable->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)');
476 $contactQueries[] = $sql;
478 // lets also store the records that are explicitly added to the group
479 // this allows us to skip the group contact LEFT JOIN
481 "SELECT $groupID as group_id, contact_id as contact_id
482 FROM civicrm_group_contact
483 WHERE civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID ";
485 self
::clearGroupContactCache($groupID);
487 foreach ($contactQueries as $contactQuery) {
488 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) {$contactQuery}");
491 if ($group->children
) {
493 // Store a list of contacts who are removed from the parent group
494 $sqlContactsRemovedFromGroup = "
496 FROM civicrm_group_contact
497 WHERE civicrm_group_contact.status = 'Removed'
498 AND civicrm_group_contact.group_id = $groupID ";
499 $dao = CRM_Core_DAO
::executeQuery($sqlContactsRemovedFromGroup);
500 $removed_contacts = [];
501 while ($dao->fetch()) {
502 $removed_contacts[] = $dao->contact_id
;
505 $childrenIDs = explode(',', $group->children
);
506 foreach ($childrenIDs as $childID) {
507 $contactIDs = CRM_Contact_BAO_Group
::getMember($childID, FALSE);
508 // Unset each contact that is removed from the parent group
509 foreach ($removed_contacts as $removed_contact) {
510 unset($contactIDs[$removed_contact]);
512 if (empty($contactIDs)) {
513 // This child group has no contact IDs so we don't need to add them to
517 foreach ($contactIDs as $contactID => $dontCare) {
518 $values[] = "({$groupID},{$contactID})";
520 $str = implode(',', $values);
521 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) VALUES $str");
525 // grab a lock so other processes don't compete and do the same query
526 $lock = Civi
::lockManager()->acquire("data.core.group.{$groupID}");
527 if (!$lock->isAcquired()) {
528 // this can cause inconsistent results since we don't know if the other process
529 // will fill up the cache before our calling routine needs it.
530 // however this routine does not return the status either, so basically
531 // its a "lets return and hope for the best"
535 // Don't call clearGroupContactCache as we don't want to clear the cache dates
536 // The will get updated by updateCacheTime() below and not clearing the dates reduces
537 // the chance that loadAll() will try and rebuild at the same time.
540 FROM civicrm_group_contact_cache g
541 WHERE g.group_id = %1 ";
543 1 => [$groupID, 'Integer'],
545 CRM_Core_DAO
::executeQuery($clearCacheQuery, $params);
547 CRM_Core_DAO
::executeQuery(
548 "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
549 SELECT DISTINCT contact_id, group_id FROM $tempTable
551 $groupContactsTempTable->drop();
552 self
::updateCacheTime([$groupID], TRUE);
558 * Retrieve the smart group cache timeout in minutes.
560 * This checks if a timeout has been configured. If one has then smart groups should not
561 * be refreshed more frequently than the time out. If a group was recently refreshed it should not
562 * refresh again within that period.
566 public static function smartGroupCacheTimeout() {
567 $config = CRM_Core_Config
::singleton();
570 isset($config->smartGroupCacheTimeout
) &&
571 is_numeric($config->smartGroupCacheTimeout
)
573 return $config->smartGroupCacheTimeout
;
576 // Default to 5 minutes.
581 * Get all the smart groups that this contact belongs to.
583 * Note that this could potentially be a super slow function since
584 * it ensure that all contact groups are loaded in the cache
586 * @param int $contactID
587 * @param bool $showHidden
588 * Hidden groups are shown only if this flag is set.
591 * an array of groups that this contact belongs to
593 public static function contactGroup($contactID, $showHidden = FALSE) {
594 if (empty($contactID)) {
598 if (is_array($contactID)) {
599 $contactIDs = $contactID;
602 $contactIDs = [$contactID];
609 $hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) ';
612 $contactIDString = CRM_Core_DAO
::escapeString(implode(', ', $contactIDs));
614 SELECT gc.group_id, gc.contact_id, g.title, g.children, g.description
615 FROM civicrm_group_contact_cache gc
616 INNER JOIN civicrm_group g ON g.id = gc.group_id
617 WHERE gc.contact_id IN ($contactIDString)
619 ORDER BY gc.contact_id, g.children
622 $dao = CRM_Core_DAO
::executeQuery($sql);
624 $prevContactID = NULL;
625 while ($dao->fetch()) {
628 $prevContactID != $dao->contact_id
630 $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
632 $prevContactID = $dao->contact_id
;
633 if (!array_key_exists($dao->contact_id
, $contactGroup)) {
634 $contactGroup[$dao->contact_id
]
635 = ['group' => [], 'groupTitle' => []];
638 $contactGroup[$dao->contact_id
]['group'][]
640 'id' => $dao->group_id
,
641 'title' => $dao->title
,
642 'description' => $dao->description
,
643 'children' => $dao->children
,
645 $contactGroup[$dao->contact_id
]['groupTitle'][] = $dao->title
;
648 if ($prevContactID) {
649 $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
652 if ((!empty($contactGroup[$contactID]) && is_numeric($contactID))) {
653 return $contactGroup[$contactID];
656 return $contactGroup;
661 * Get the datetime from which the cache should be considered invalid.
663 * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was
664 * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago.
668 public static function getCacheInvalidDateTime() {
669 return date('YmdHis', strtotime("-" . self
::smartGroupCacheTimeout() . " Minutes"));
673 * Get the date when the cache should be refreshed from.
675 * Ie. now + the offset & we will delete anything prior to then.
679 public static function getRefreshDateTime() {
680 return date('YmdHis', strtotime("+ " . self
::smartGroupCacheTimeout() . " Minutes"));
684 * Invalidates the smart group cache for a particular group
685 * @param int $groupID - Group to invalidate
687 public static function invalidateGroupContactCache($groupID) {
688 CRM_Core_DAO
::executeQuery("UPDATE civicrm_group
689 SET cache_date = NULL, refresh_date = NULL
691 1 => [$groupID, 'Positive'],
696 * @param array $savedSearch
697 * @param string $addSelect
698 * @param string $excludeClause
700 * @throws API_Exception
701 * @throws \Civi\API\Exception\NotImplementedException
702 * @throws CRM_Core_Exception
704 protected static function getApiSQL(array $savedSearch, string $addSelect, string $excludeClause) {
705 $apiParams = $savedSearch['api_params'] +
['select' => ['id'], 'checkPermissions' => FALSE];
706 $idField = SqlExpression
::convert($apiParams['select'][0], TRUE)->getAlias();
707 // Unless there's a HAVING clause, we don't care about other columns
708 if (empty($apiParams['having'])) {
709 $apiParams['select'] = array_slice($apiParams['select'], 0, 1);
711 $api = \Civi\API\Request
::create($savedSearch['api_entity'], 'get', $apiParams);
712 $query = new \Civi\Api4\Query\
Api4SelectQuery($api);
713 $query->forceSelectId
= FALSE;
714 $query->getQuery()->having("$idField $excludeClause");
715 $sql = $query->getSql();
716 // Place sql in a nested sub-query, otherwise HAVING is impossible on any field other than contact_id
717 return "SELECT $addSelect, `$idField` AS contact_id FROM ($sql) api_query";
721 * Get sql from a custom search.
723 * We split it up and store custom class
724 * so temp tables are not destroyed if they are used
726 * @param int $savedSearchID
727 * @param array $ssParams
728 * @param string $addSelect
729 * @param string $excludeClause
734 protected static function getCustomSearchSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
735 $searchSQL = CRM_Contact_BAO_SearchCustom
::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs();
736 $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
737 if (strpos($searchSQL, 'WHERE') === FALSE) {
738 $searchSQL .= " WHERE contact_a.id $excludeClause";
741 $searchSQL .= " AND contact_a.id $excludeClause";
743 return preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $searchSQL);
747 * Get array of sql from a saved query object group.
749 * @param int $savedSearchID
750 * @param array $ssParams
751 * @param string $addSelect
752 * @param string $excludeClause
755 * @throws \CRM_Core_Exception
756 * @throws \CiviCRM_API3_Exception
758 protected static function getQueryObjectSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
759 $returnProperties = NULL;
760 if (CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
761 $fv = CRM_Contact_BAO_SavedSearch
::getFormValues($savedSearchID);
762 $returnProperties = CRM_Core_BAO_Mapping
::returnProperties($fv);
764 $formValues = CRM_Contact_BAO_SavedSearch
::getFormValues($savedSearchID);
765 // CRM-17075 using the formValues in this way imposes extra logic and complexity.
766 // we have the where_clause and where tables stored in the saved_search table
767 // and should use these rather than re-processing the form criteria (which over-works
768 // the link between the form layer & the query layer too).
769 // It's hard to think of when you would want to use anything other than return
770 // properties = array('contact_id' => 1) here as the point would appear to be to
771 // generate the list of contact ids in the group.
772 // @todo review this to use values in saved_search table (preferably for 4.8).
774 = new CRM_Contact_BAO_Query(
775 $ssParams, $returnProperties, NULL,
779 $formValues['display_relationship_type'] ??
NULL,
780 $formValues['operator'] ??
'AND'
782 $query->_useDistinct
= FALSE;
783 $query->_useGroupBy
= FALSE;
784 $sqlParts = $query->getSearchSQLParts(
788 "contact_a.id $excludeClause"
790 $select = preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $sqlParts['select']);
792 return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}";