/** * Moves existing tags associated with an item type to another tag collection * * @param string $component * @param string $itemtype * @param int $tagcollid */ public static function move_tags($component, $itemtype, $tagcollid) { global $DB; $params = array('itemtype1' => $itemtype, 'component1' => $component, 'itemtype2' => $itemtype, 'component2' => $component, 'tagcollid1' => $tagcollid, 'tagcollid2' => $tagcollid); // Find all collections that need to be cleaned later. $sql = "SELECT DISTINCT t.tagcollid " . "FROM {tag_instance} ti " . "JOIN {tag} t ON t.id = ti.tagid AND t.tagcollid <> :tagcollid1 " . "WHERE ti.itemtype = :itemtype2 AND ti.component = :component2 "; $cleanupcollections = $DB->get_fieldset_sql($sql, $params); // Find all tags that are related to the tags being moved and make sure they are present in the target tagcoll. // This query is a little complicated because Oracle does not allow to run SELECT DISTINCT on CLOB fields. $sql = "SELECT name, rawname, description, descriptionformat, userid, tagtype, flag " . "FROM {tag} WHERE id IN " . "(SELECT r.id " . "FROM {tag_instance} ti " . "JOIN {tag} t ON t.id = ti.tagid AND t.tagcollid <> :tagcollid1 " . "JOIN {tag_instance} tr ON tr.itemtype = 'tag' and tr.component = 'core' AND tr.itemid = t.id " . "JOIN {tag} r ON r.id = tr.tagid " . "LEFT JOIN {tag} re ON re.name = r.name AND re.tagcollid = :tagcollid2 " . "WHERE ti.itemtype = :itemtype2 AND ti.component = :component2 " . " AND re.id IS NULL)"; // We need related tags that ARE NOT present in the target tagcoll. $result = $DB->get_records_sql($sql, $params); foreach ($result as $tag) { $tag->tagcollid = $tagcollid; $tag->id = $DB->insert_record('tag', $tag); \core\event\tag_created::create_from_tag($tag); } // Find all tags that need moving and have related tags, remember their related tags. $sql = "SELECT t.name AS tagname, r.rawname AS relatedtag " . "FROM {tag_instance} ti " . "JOIN {tag} t ON t.id = ti.tagid AND t.tagcollid <> :tagcollid1 " . "JOIN {tag_instance} tr ON t.id = tr.tagid AND tr.itemtype = 'tag' and tr.component = 'core' " . "JOIN {tag} r ON r.id = tr.itemid " . "WHERE ti.itemtype = :itemtype2 AND ti.component = :component2 " . "ORDER BY t.id, tr.ordering "; $relatedtags = array(); $result = $DB->get_recordset_sql($sql, $params); foreach ($result as $record) { $relatedtags[$record->tagname][] = $record->relatedtag; } $result->close(); // Find all tags that are used for this itemtype/component and are not present in the target tag collection. // This query is a little complicated because Oracle does not allow to run SELECT DISTINCT on CLOB fields. $sql = "SELECT id, name, rawname, description, descriptionformat, userid, tagtype, flag\n FROM {tag} WHERE id IN\n (SELECT t.id\n FROM {tag_instance} ti\n JOIN {tag} t ON t.id = ti.tagid AND t.tagcollid <> :tagcollid1\n LEFT JOIN {tag} tt ON tt.name = t.name AND tt.tagcollid = :tagcollid2\n WHERE ti.itemtype = :itemtype2 AND ti.component = :component2\n AND tt.id IS NULL)"; $movedtags = array(); // Keep track of moved tags so we don't hit DB index violation. $result = $DB->get_records_sql($sql, $params); foreach ($result as $tag) { $originaltagid = $tag->id; if (array_key_exists($tag->name, $movedtags)) { // Case of corrupted data when the same tag was in several collections. $tag->id = $movedtags[$tag->name]; } else { // Copy the tag into the new collection. unset($tag->id); $tag->tagcollid = $tagcollid; $tag->id = $DB->insert_record('tag', $tag); \core\event\tag_created::create_from_tag($tag); $movedtags[$tag->name] = $tag->id; } $DB->execute("UPDATE {tag_instance} SET tagid = ? WHERE tagid = ? AND itemtype = ? AND component = ?", array($tag->id, $originaltagid, $itemtype, $component)); } // Find all tags that are used for this itemtype/component and are already present in the target tag collection. $sql = "SELECT DISTINCT t.id, tt.id AS targettagid\n FROM {tag_instance} ti\n JOIN {tag} t ON t.id = ti.tagid AND t.tagcollid <> :tagcollid1\n JOIN {tag} tt ON tt.name = t.name AND tt.tagcollid = :tagcollid2\n WHERE ti.itemtype = :itemtype2 AND ti.component = :component2"; $result = $DB->get_records_sql($sql, $params); foreach ($result as $tag) { $DB->execute("UPDATE {tag_instance} SET tagid = ? WHERE tagid = ? AND itemtype = ? AND component = ?", array($tag->targettagid, $tag->id, $itemtype, $component)); } // Add related tags to the moved tags. if ($relatedtags) { $tags = core_tag_tag::get_by_name_bulk($tagcollid, array_keys($relatedtags)); foreach ($tags as $tag) { $tag->add_related_tags($relatedtags[$tag->name]); } } if ($cleanupcollections) { core_tag_collection::cleanup_unused_tags($cleanupcollections); } // Reset caches. cache::make('core', 'tags')->delete('tag_area'); }