string $selectValue) */ private static $mappings = [ 'a_b' => [ 'relationship_id' => 'rel.id', 'relationship_type_id' => 'rel.relationship_type_id', 'orientation' => '"a_b"', 'near_contact_id' => 'rel.contact_id_a', 'near_relation' => 'reltype.name_a_b', 'far_contact_id' => 'rel.contact_id_b', 'far_relation' => 'reltype.name_b_a', 'start_date' => 'rel.start_date', 'end_date' => 'rel.end_date', 'is_active' => 'rel.is_active', ], 'b_a' => [ 'relationship_id' => 'rel.id', 'relationship_type_id' => 'rel.relationship_type_id', 'orientation' => '"b_a"', 'near_contact_id' => 'rel.contact_id_b', 'near_relation' => 'reltype.name_b_a', 'far_contact_id' => 'rel.contact_id_a', 'far_relation' => 'reltype.name_a_b', 'start_date' => 'rel.start_date', 'end_date' => 'rel.end_date', 'is_active' => 'rel.is_active', ], ]; /** * A list of fields which uniquely identify a row. * * @var array */ private static $keyFields = ['relationship_id', 'orientation']; /** * A list of of fields in `civicrm_relationship_type` which (if changed) * will necessitate an update to the cache. * * @var array */ private static $relTypeWatchFields = ['name_a_b', 'name_b_a']; /** * Add our list of triggers to the global list. * * @param \Civi\Core\Event\GenericHookEvent $e * @see \CRM_Utils_Hook::triggerInfo */ public static function onHookTriggerInfo($e) { $relUpdates = self::createInsertUpdateQueries(); // Use utf8mb4_bin or utf8_bin, depending on what's in use. $collation = preg_replace('/^(utf8(?:mb4)?)_.*$/', '$1_bin', CRM_Core_BAO_SchemaHandler::getInUseCollation()); foreach ($relUpdates as $relUpdate) { /** * This trigger runs whenever a "civicrm_relationship" record is inserted or updated. * * Goal: Ensure that every relationship record has two corresponding entries in the * cache, the forward relationship (A=>B) and reverse relationship (B=>A). */ $triggers[] = [ 'table' => 'civicrm_relationship', 'when' => 'AFTER', 'event' => ['INSERT', 'UPDATE'], 'sql' => $relUpdate->copy()->where('rel.id = NEW.id')->toSQL() . ";\n", ]; $triggers[] = [ /** * This trigger runs whenever a "civicrm_relationship_type" record is updated. * * Goal: Ensure that the denormalized fields ("name_b_a"/"name_a_b" <=> "relation") remain current. */ 'table' => 'civicrm_relationship_type', 'when' => 'AFTER', 'event' => ['UPDATE'], 'sql' => sprintf("\nIF (%s) THEN\n %s;\n END IF;\n", // Condition implode(' OR ', array_map(function ($col) use ($collation) { return "(OLD.$col != NEW.$col COLLATE $collation)"; }, self::$relTypeWatchFields)), // Action $relUpdate->copy()->where('rel.relationship_type_id = NEW.id')->toSQL() ), ]; } // Note: We do not need a DELETE trigger to maintain `civicrm_relationship_cache` because it uses `CASCADE`. $st = new \Civi\Core\SqlTrigger\StaticTriggers($triggers); $st->onTriggerInfo($e); } /** * Read all records from civicrm_relationship and populate the cache. * Each ordinary relationship in `civicrm_relationship` becomes two * distinct records in the cache (one for A=>B relations; and one for B=>A). * * This method is primarily written (a) for manual testing and (b) in case * a broken DBMS, screwy import, buggy code, etc causes a corruption. * * NOTE: This is closely related to FiveTwentyNine::populateRelationshipCache(), * except that the upgrader users pagination. */ public static function rebuild() { $relUpdates = self::createInsertUpdateQueries(); CRM_Core_DAO::executeQuery('TRUNCATE civicrm_relationship_cache'); foreach ($relUpdates as $relUpdate) { $relUpdate->execute(); } } /** * Prepare a list of SQL queries that map data from civicrm_relationship * to civicrm_relationship_cache. * * @return CRM_Utils_SQL_Select[] * A list of SQL queries - one for each mapping. */ public static function createInsertUpdateQueries() { $queries = []; foreach (self::$mappings as $name => $mapping) { $queries[$name] = CRM_Utils_SQL_Select::from('civicrm_relationship rel') ->join('reltype', 'INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id') ->syncInto('civicrm_relationship_cache', self::$keyFields, $mapping); } return $queries; } }