MySQL альтернатива по дублированию ключа обновления

Question

Я пытаюсь вытащить несколько таблиц для вставки в таблицу для создания назначений ролей в базе данных moodle на основе созданных категорий, но мне нужно, чтобы она обновлялась по дублированному ключу, но я не могу использовать ON DUPLICATE KEY UPDATE, потому что поля im пытаются соответствие по идентификатору роли, идентификатору контекста и идентификатору пользователя не являются первичными ключами в таблице mdl_role_assignments.

insert into vclassmoodle.mdl_role_assignments (roleid,contextid,userid,timemodified,modifierid,itemid,sortorder) select 
mdl_role.id as roleid, mdl_context.id as contextid, mdl_user.id as userid, unix_timestamp() as timemodified, 3 as modifierid, 0 as itemid, 0 as sortorder
from
mdl_context
    left join
mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
    left join
mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
    join
mdl_role ON mdl_role.shortname = 'manager'
where mdl_context.contextlevel = 40 and mdl_course_categories.depth > 1

Дайте мне знать, если мне нужно что-то уточнить

Спасибо

Answer 1

Только что посмотрел на функцию role_assign() в /lib/accesslib.php

Если есть дубликат, он не обновляется, поэтому вы можете просто игнорировать дубликаты.

Хотя вам действительно следует использовать функцию role_assign(), а не вставлять данные напрямую. В случае, если назначение роли изменится в будущем, но также и потому, что оно вызывает событие role_assigned, которое может использоваться в другом месте.

Все еще используйте ваш запрос, но игнорируйте существующие записи и создайте цикл для вызова role_assign(), что-то вроде этого

SELECT mdl_role.id as roleid,
       mdl_context.id as contextid,
       mdl_user.id as userid
FROM mdl_context
JOIN mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
JOIN mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
JOIN mdl_role ON mdl_role.shortname = 'manager'
WHERE mdl_context.contextlevel = 40
AND mdl_course_categories.depth > 1
AND NOT EXISTS (
    SELECT mdl_role_assignments.id
    FROM mdl_role_assignments
    WHERE mdl_role_assignments.roleid = mdl_role.id
    AND mdl_role_assignments.contextid = mdl_context.id
    AND mdl_role_assignments.userid = mdl_user.id
    AND mdl_role_assignments.itemid = 0
    AND mdl_role_assignments.component = '')

Обратите внимание, что дубликат представляет собой комбинацию roleid, userid и contextxtid, но также является компонентом и itemid. Поэтому компонент = '' тоже нужно проверить.