Обновление промежуточной таблицы Best Practice PHP, MySQL
Скажем, у меня есть отношения «многие ко многим» между двумя учениками и предметами. Таким образом, мы создаем промежуточную таблицу для отображения данных. Допустим, это ученики. Таблица Students_subjects имеет 2 столбца, который является student_id & subject_id.
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| | |
+------------+--------------+
+------------+--------------+
| subject_id | subject_name |
+------------+--------------+
| | |
+------------+--------------+
+------------+------------+
| student_id | subject_id |
+------------+------------+
| | |
+------------+------------+
До недавнего времени, когда происходило обновление в Students_subjects (учащийся, выбирающий предметы), я занимался удалением всех предметов, в которых у учащегося были вставлены новые.
+------------+------------+----------+
| student_id | subject_id | selected |
+------------+------------+----------+
| 1 | 2 | 1 |
+------------+------------+----------+
| 1 | 2 | 0 |
+------------+------------+----------+
Но недавно я добавил новый столбец в эту промежуточную таблицу как выбранный. Поэтому, если студент выбрал новые предметы и отменил выбор некоторых старых предметов, я добавил активные для новых и неактивные для отмены. (Я делаю это в PHP, получая новые выбранные предметы и ранее имевшие предметы. Затем сравниваю оба. Когда ученик выбирает предметы, я проверяю все предметы, которые ученик имеет [активные и неактивные]. Если вновь выбранные предметы ранее не были активными предметы я их активирую и добавляю новые.)
Какова лучшая практика для этого? Есть ли лучший способ добиться этого?
Решение
Я действительно думаю, что ваш первый подход (удаление всех связанных строк и вставка новых записей) намного проще.
Если все еще хотите иметь selected поле, простой способ будет создать уникальный составной индекс и использовать INSERT ... ON DUPLICATE KEY UPDATE, Это также может предотвратить случайную вставку того же user_id а также subject_id пара. Сначала создайте индекс:
ALTER TABLE `students_subjects` ADD UNIQUE `unique_student_subject`(`student_id`, `subject_id`);
Затем просто обновите selected поле для студента 0 и вставьте предметы, выбранные студентом. Пример использования PDO:
$studentId = 1;
$newlySelectedSubjects[] = ['subject_id' => 3];
$newlySelectedSubjects[] = ['subject_id' => 4];
$db = new PDO("connection string here", $username, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->beginTransaction();
try{
$qry = 'UPDATE students_subjects SET selected = 0 WHERE student_id = :studentId';
$stmt = $db->prepare($qry);
$stmt->execute(['studentId' => $studentId]);
$insertQry = 'INSERT INTO students_subjects (student_id, subject_id, selected)'
.'VALUES (:studentId, :subjectId, 1) '
.'ON DUPLICATE KEY UPDATE '
.'selected = 1';
$insertStmt = $db->prepare($insertQry);
foreach ($newlySelectedSubjects as $subject) {
$params = ['studentId' => $studentId, 'subjectId' => $subject['subject_id']];
$insertStmt->execute($params);
}
$db->commit();
} catch (PDOException $e) {
//do something with exception
$db->rollBack();
}
Что произойдет, если студент с student_id знак равно 1 а также subject_id знак равно 3 уже существует в таблице, selected поле будет установлено в 1, если не новая запись будет вставлена.
Если вам требуется отслеживать выбор и отмену пользовательских тем, я предлагаю реализовать какие-то контрольные журналы и оставить students_subjects держать только отношения между студентами и предметами. Это может быть проще с помощью MySQL TRIGGER. Упрощенный пример таблицы контрольного журнала:
+------------+------------+----------+---------------------+
| student_id | subject_id | action | action_datetime |
+------------+------------+----------+---------------------+
| 1 | 2 | remove | 2018-01-01 00:01:23 |
+------------+------------+----------+---------------------+
| 1 | 4 | add | 2018-01-01 01:07:45 |
+------------+------------+----------+---------------------+
Другие решения
Других решений пока нет …