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
19 * The CiviCRM duplicate discovery engine is based on an
20 * algorithm designed by David Strauss <david@fourkitchens.com>.
22 class CRM_Dedupe_BAO_DedupeRuleGroup
extends CRM_Dedupe_DAO_DedupeRuleGroup
{
25 * Ids of the contacts to limit the SQL queries (whole-database queries otherwise)
28 public $contactIds = [];
31 * Set the contact IDs to restrict the dedupe to.
33 * @param array $contactIds
35 public function setContactIds($contactIds) {
36 $this->contactIds
= $contactIds;
40 * Params to dedupe against (queries against the whole contact set otherwise)
46 * If there are no rules in rule group.
49 public $noRules = FALSE;
51 protected $temporaryTables = [];
54 * Return a structure holding the supported tables, fields and their titles
56 * @param string $requestedType
57 * The requested contact type.
60 * a table-keyed array of field-keyed arrays holding supported fields' titles
62 public static function supportedFields($requestedType) {
63 if (!isset(Civi
::$statics[__CLASS__
]['supportedFields'])) {
64 // this is needed, as we're piggy-backing importableFields() below
66 'civicrm_country.name' => 'civicrm_address.country_id',
67 'civicrm_county.name' => 'civicrm_address.county_id',
68 'civicrm_state_province.name' => 'civicrm_address.state_province_id',
69 'gender.label' => 'civicrm_contact.gender_id',
70 'individual_prefix.label' => 'civicrm_contact.prefix_id',
71 'individual_suffix.label' => 'civicrm_contact.suffix_id',
72 'addressee.label' => 'civicrm_contact.addressee_id',
73 'email_greeting.label' => 'civicrm_contact.email_greeting_id',
74 'postal_greeting.label' => 'civicrm_contact.postal_greeting_id',
75 'civicrm_phone.phone' => 'civicrm_phone.phone_numeric',
77 // the table names we support in dedupe rules - a filter for importableFields()
89 foreach (CRM_Contact_BAO_ContactType
::basicTypes() as $ctype) {
90 // take the table.field pairs and their titles from importableFields() if the table is supported
91 foreach (CRM_Contact_BAO_Contact
::importableFields($ctype) as $iField) {
92 if (isset($iField['where'])) {
93 $where = $iField['where'];
94 if (isset($replacements[$where])) {
95 $where = $replacements[$where];
97 list($table, $field) = explode('.', $where);
98 if (!in_array($table, $supportedTables)) {
101 $fields[$ctype][$table][$field] = $iField['title'];
104 // Note that most of the fields available come from 'importable fields' -
105 // I thought about making this field 'importable' but it felt like there might be unknown consequences
106 // so I opted for just adding it in & securing it with a unit test.
107 /// Example usage of sort_name - It is possible to alter sort name via hook so 2 organization names might differ as in
108 // Justice League vs The Justice League but these could have the same sort_name if 'the the'
109 // exension is installed (https://github.com/eileenmcnaughton/org.wikimedia.thethe)
110 $fields[$ctype]['civicrm_contact']['sort_name'] = ts('Sort Name');
112 // add all custom data fields including those only for sub_types.
113 foreach (CRM_Core_BAO_CustomGroup
::getTree($ctype, NULL, NULL, -1, [], NULL, TRUE, NULL, TRUE) as $key => $cg) {
117 foreach ($cg['fields'] as $cf) {
118 $fields[$ctype][$cg['table_name']][$cf['column_name']] = $cg['title'] . ' : ' . $cf['label'];
122 //Does this have to run outside of cache?
123 CRM_Utils_Hook
::dupeQuery(CRM_Core_DAO
::$_nullObject, 'supportedFields', $fields);
124 Civi
::$statics[__CLASS__
]['supportedFields'] = $fields;
127 return Civi
::$statics[__CLASS__
]['supportedFields'][$requestedType] ??
[];
132 * Return the SQL query for dropping the temporary table.
134 public function tableDropQuery() {
135 return 'DROP TEMPORARY TABLE IF EXISTS dedupe';
139 * Return a set of SQL queries whose cummulative weights will mark matched
140 * records for the RuleGroup::threasholdQuery() to retrieve.
142 public function tableQuery() {
143 // make sure we've got a fetched dbrecord, not sure if this is enforced
144 if (!$this->name
== NULL ||
$this->is_reserved
== NULL) {
148 // Reserved Rule Groups can optionally get special treatment by
149 // implementing an optimization class and returning a query array.
150 if ($this->is_reserved
&&
151 CRM_Utils_File
::isIncludable("CRM/Dedupe/BAO/QueryBuilder/{$this->name}.php")
153 $command = empty($this->params
) ?
'internal' : 'record';
154 $queries = call_user_func(["CRM_Dedupe_BAO_QueryBuilder_{$this->name}", $command], $this);
157 // All other rule groups have queries generated by the member dedupe
158 // rules defined in the administrative interface.
160 // Find all rules contained by this script sorted by weight so that
161 // their execution can be short circuited on RuleGroup::fillTable()
162 $bao = new CRM_Dedupe_BAO_DedupeRule();
163 $bao->dedupe_rule_group_id
= $this->id
;
164 $bao->orderBy('rule_weight DESC');
167 // Generate a SQL query for each rule in the rule group that is
168 // tailored to respect the param and contactId options provided.
170 while ($bao->fetch()) {
171 $bao->contactIds
= $this->contactIds
;
172 $bao->params
= $this->params
;
174 // Skipping empty rules? Empty rules shouldn't exist; why check?
175 if ($query = $bao->sql()) {
176 $queries["{$bao->rule_table}.{$bao->rule_field}.{$bao->rule_weight}"] = $query;
181 // if there are no rules in this rule group
182 // add an empty query fulfilling the pattern
184 $this->noRules
= TRUE;
191 public function fillTable() {
192 // get the list of queries handy
193 $tableQueries = $this->tableQuery();
195 if ($this->params
&& !$this->noRules
) {
196 $this->temporaryTables
['dedupe'] = CRM_Utils_SQL_TempTable
::build()
197 ->setCategory('dedupe')
198 ->createWithColumns("id1 int, weight int, UNIQUE UI_id1 (id1)")->getName();
199 $dedupeCopyTemporaryTableObject = CRM_Utils_SQL_TempTable
::build()
200 ->setCategory('dedupe');
201 $this->temporaryTables
['dedupe_copy'] = $dedupeCopyTemporaryTableObject->getName();
202 $insertClause = "INSERT INTO {$this->temporaryTables['dedupe']} (id1, weight)";
203 $groupByClause = "GROUP BY id1, weight";
204 $dupeCopyJoin = " JOIN {$this->temporaryTables['dedupe_copy']} ON {$this->temporaryTables['dedupe_copy']}.id1 = t1.column WHERE ";
207 $this->temporaryTables
['dedupe'] = CRM_Utils_SQL_TempTable
::build()
208 ->setCategory('dedupe')
209 ->createWithColumns("id1 int, id2 int, weight int, UNIQUE UI_id1_id2 (id1, id2)")->getName();
210 $dedupeCopyTemporaryTableObject = CRM_Utils_SQL_TempTable
::build()
211 ->setCategory('dedupe');
212 $this->temporaryTables
['dedupe_copy'] = $dedupeCopyTemporaryTableObject->getName();
213 $insertClause = "INSERT INTO {$this->temporaryTables['dedupe']} (id1, id2, weight)";
214 $groupByClause = "GROUP BY id1, id2, weight";
215 $dupeCopyJoin = " JOIN {$this->temporaryTables['dedupe_copy']} ON {$this->temporaryTables['dedupe_copy']}.id1 = t1.column AND {$this->temporaryTables['dedupe_copy']}.id2 = t2.column WHERE ";
217 $patternColumn = '/t1.(\w+)/';
220 CRM_Utils_Hook
::dupeQuery($this, 'table', $tableQueries);
222 while (!empty($tableQueries)) {
223 list($isInclusive, $isDie) = self
::isQuerySetInclusive($tableQueries, $this->threshold
, $exclWeightSum);
226 // order queries by table count
227 self
::orderByTableCount($tableQueries);
229 $weightSum = array_sum($exclWeightSum);
230 $searchWithinDupes = !empty($exclWeightSum) ?
1 : 0;
232 while (!empty($tableQueries)) {
233 // extract the next query ( and weight ) to be executed
234 $fieldWeight = array_keys($tableQueries);
235 $fieldWeight = $fieldWeight[0];
236 $query = array_shift($tableQueries);
238 if ($searchWithinDupes) {
239 // drop dedupe_copy table just in case if its already there.
240 $dedupeCopyTemporaryTableObject->drop();
241 // get prepared to search within already found dupes if $searchWithinDupes flag is set
242 $dedupeCopyTemporaryTableObject->createWithQuery("SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}");
244 preg_match($patternColumn, $query, $matches);
245 $query = str_replace(' WHERE ', str_replace('column', $matches[1], $dupeCopyJoin), $query);
247 // CRM-19612: If there's a union, there will be two WHEREs, and you
248 // can't use the temp table twice.
249 if (preg_match('/' . $this->temporaryTables
['dedupe_copy'] . '[\S\s]*(union)[\S\s]*' . $this->temporaryTables
['dedupe_copy'] . '/i', $query, $matches, PREG_OFFSET_CAPTURE
)) {
250 // Make a second temp table:
251 $this->temporaryTables
['dedupe_copy_2'] = CRM_Utils_SQL_TempTable
::build()
252 ->setCategory('dedupe')
253 ->createWithQuery("SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}")
255 // After the union, use that new temp table:
256 $part1 = substr($query, 0, $matches[1][1]);
257 $query = $part1 . str_replace($this->temporaryTables
['dedupe_copy'], $this->temporaryTables
['dedupe_copy_2'], substr($query, $matches[1][1]));
260 $searchWithinDupes = 1;
262 // construct and execute the intermediate query
263 $query = "{$insertClause} {$query} {$groupByClause} ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)";
264 $dao = CRM_Core_DAO
::executeQuery($query);
266 // FIXME: we need to be more acurate with affected rows, especially for insert vs duplicate insert.
267 // And that will help optimize further.
268 $affectedRows = $dao->affectedRows();
270 // In an inclusive situation, failure of any query means no further processing -
271 if ($affectedRows == 0) {
272 // reset to make sure no further execution is done.
276 $weightSum = substr($fieldWeight, strrpos($fieldWeight, '.') +
1) +
$weightSum;
278 // An exclusive situation -
281 // since queries are already sorted by weights, we can continue as is
282 $fieldWeight = array_keys($tableQueries);
283 $fieldWeight = $fieldWeight[0];
284 $query = array_shift($tableQueries);
285 $query = "{$insertClause} {$query} {$groupByClause} ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)";
286 $dao = CRM_Core_DAO
::executeQuery($query);
287 if ($dao->affectedRows() >= 1) {
288 $exclWeightSum[] = substr($fieldWeight, strrpos($fieldWeight, '.') +
1);
292 // its a die situation
299 * Function to determine if a given query set contains inclusive or exclusive set of weights.
300 * The function assumes that the query set is already ordered by weight in desc order.
301 * @param $tableQueries
303 * @param array $exclWeightSum
307 public static function isQuerySetInclusive($tableQueries, $threshold, $exclWeightSum = []) {
309 foreach ($tableQueries as $key => $query) {
310 $input[] = substr($key, strrpos($key, '.') +
1);
313 if (!empty($exclWeightSum)) {
314 $input = array_merge($input, $exclWeightSum);
318 if (count($input) == 1) {
319 return [FALSE, $input[0] < $threshold];
322 $totalCombinations = 0;
323 for ($i = 0; $i < count($input); $i++
) {
324 $combination = [$input[$i]];
325 if (array_sum($combination) >= $threshold) {
326 $totalCombinations++
;
329 for ($j = $i +
1; $j < count($input); $j++
) {
330 $combination[] = $input[$j];
331 if (array_sum($combination) >= $threshold) {
332 $totalCombinations++
;
336 return [$totalCombinations == 1, $totalCombinations <= 0];
340 * sort queries by number of records for the table associated with them.
341 * @param $tableQueries
343 public static function orderByTableCount(&$tableQueries) {
344 static $tableCount = [];
347 foreach ($tableQueries as $key => $query) {
348 $table = explode(".", $key);
350 if (!array_key_exists($table, $tableCount)) {
351 $query = "SELECT COUNT(*) FROM {$table}";
352 $tableCount[$table] = CRM_Core_DAO
::singleValueQuery($query);
354 $tempArray[$key] = $tableCount[$table];
358 foreach ($tempArray as $key => $count) {
359 $tempArray[$key] = $tableQueries[$key];
361 $tableQueries = $tempArray;
365 * Return the SQL query for getting only the interesting results out of the dedupe table.
367 * @$checkPermission boolean $params a flag to indicate if permission should be considered.
368 * default is to always check permissioning but public pages for example might not want
369 * permission to be checked for anonymous users. Refer CRM-6211. We might be beaking
370 * Multi-Site dedupe for public pages.
372 * @param bool $checkPermission
376 public function thresholdQuery($checkPermission = TRUE) {
377 $this->_aclFrom
= '';
380 if ($this->params
&& !$this->noRules
) {
381 if ($checkPermission) {
382 list($this->_aclFrom
, $aclWhere) = CRM_Contact_BAO_Contact_Permission
::cacheClause('civicrm_contact');
383 $aclWhere = $aclWhere ?
"AND {$aclWhere}" : '';
385 $query = "SELECT {$this->temporaryTables['dedupe']}.id1 as id
386 FROM {$this->temporaryTables['dedupe']} JOIN civicrm_contact ON {$this->temporaryTables['dedupe']}.id1 = civicrm_contact.id {$this->_aclFrom}
387 WHERE contact_type = '{$this->contact_type}' AND is_deleted = 0 $aclWhere
388 AND weight >= {$this->threshold}";
392 if ($checkPermission) {
393 list($this->_aclFrom
, $aclWhere) = CRM_Contact_BAO_Contact_Permission
::cacheClause(['c1', 'c2']);
394 $aclWhere = $aclWhere ?
"AND {$aclWhere}" : '';
396 $query = "SELECT IF({$this->temporaryTables['dedupe']}.id1 < {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id1, {$this->temporaryTables['dedupe']}.id2) as id1,
397 IF({$this->temporaryTables['dedupe']}.id1 < {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id1) as id2, {$this->temporaryTables['dedupe']}.weight
398 FROM {$this->temporaryTables['dedupe']} JOIN civicrm_contact c1 ON {$this->temporaryTables['dedupe']}.id1 = c1.id
399 JOIN civicrm_contact c2 ON {$this->temporaryTables['dedupe']}.id2 = c2.id {$this->_aclFrom}
400 LEFT JOIN civicrm_dedupe_exception exc ON {$this->temporaryTables['dedupe']}.id1 = exc.contact_id1 AND {$this->temporaryTables['dedupe']}.id2 = exc.contact_id2
401 WHERE c1.contact_type = '{$this->contact_type}' AND
402 c2.contact_type = '{$this->contact_type}'
403 AND c1.is_deleted = 0 AND c2.is_deleted = 0
405 AND weight >= {$this->threshold} AND exc.contact_id1 IS NULL";
408 CRM_Utils_Hook
::dupeQuery($this, 'threshold', $query);
413 * find fields related to a rule group.
415 * @param array $params
418 * (rule field => weight) array and threshold associated to rule group
420 public static function dedupeRuleFieldsWeight($params) {
421 $rgBao = new CRM_Dedupe_BAO_DedupeRuleGroup();
422 $rgBao->contact_type
= $params['contact_type'];
423 if (!empty($params['id'])) {
424 // accept an ID if provided
425 $rgBao->id
= $params['id'];
428 $rgBao->used
= $params['used'];
432 $ruleBao = new CRM_Dedupe_BAO_DedupeRule();
433 $ruleBao->dedupe_rule_group_id
= $rgBao->id
;
436 while ($ruleBao->fetch()) {
437 $field_name = $ruleBao->rule_field
;
438 if ($field_name == 'phone_numeric') {
439 $field_name = 'phone';
441 $ruleFields[$field_name] = $ruleBao->rule_weight
;
444 return [$ruleFields, $rgBao->threshold
];
448 * Get all of the combinations of fields that would work with a rule.
450 * @param array $rgFields
451 * @param int $threshold
452 * @param array $combos
453 * @param array $running
455 public static function combos($rgFields, $threshold, &$combos, $running = []) {
456 foreach ($rgFields as $rgField => $weight) {
457 unset($rgFields[$rgField]);
458 $diff = $threshold - $weight;
459 $runningnow = $running;
460 $runningnow[] = $rgField;
462 self
::combos($rgFields, $diff, $combos, $runningnow);
465 $combos[] = $runningnow;
471 * Get an array of rule group id to rule group name
472 * for all th groups for that contactType. If contactType
473 * not specified, do it for all
475 * @param string $contactType
476 * Individual, Household or Organization.
480 * id => "nice name" of rule group
482 public static function getByType($contactType = NULL) {
483 $dao = new CRM_Dedupe_DAO_DedupeRuleGroup();
486 $dao->contact_type
= $contactType;
491 while ($dao->fetch()) {
492 $title = !empty($dao->title
) ?
$dao->title
: (!empty($dao->name
) ?
$dao->name
: $dao->contact_type
);
494 $name = "$title - {$dao->used}";
495 $result[$dao->id
] = $name;
501 * Get the cached contact type for a particular rule group.
503 * @param int $rule_group_id
507 public static function getContactTypeForRuleGroup($rule_group_id) {
508 if (!isset(\Civi
::$statics[__CLASS__
]) ||
!isset(\Civi
::$statics[__CLASS__
]['rule_groups'])) {
509 \Civi
::$statics[__CLASS__
]['rule_groups'] = [];
511 if (empty(\Civi
::$statics[__CLASS__
]['rule_groups'][$rule_group_id])) {
512 \Civi
::$statics[__CLASS__
]['rule_groups'][$rule_group_id]['contact_type'] = CRM_Core_DAO
::getFieldValue(
513 'CRM_Dedupe_DAO_DedupeRuleGroup',
519 return \Civi
::$statics[__CLASS__
]['rule_groups'][$rule_group_id]['contact_type'];