There is a one to many relation in the database which needs to be updated. There is a table tasks and a table users which are linked with a table task_users containing three columns: id, task_id, user_id. There could be more than one user coupled to a task at a time so there could be entries like:
id task_id user_id
0 1 20
1 1 48
2 2 20
The first part of the script deletes user_id which are not given back with the form like so:
(new \database\DataSource)->main()->query('DELETE FROM task_users WHERE', [
'task_id' => (int) $values["id"],
'user_id NOT IN' => $values["assigned_users"]
])
Here the id is a integer and the assigned_users is an array of integers This works as intended For the second part I want to insert the assigned_users which are not yet in the table, but skip the assigned_users which are already part of the table. So when user 20 and 48 were assigned before, but now I want to assign 20 and 55 to the task it should update to:
id task_id user_id
0 1 20
2 2 20
3 1 55
I have tried REPLACE INTO, INSERT INTO with a WHERE NOT IN clause, and INSERT INTO SELECT * FROM task_users WHERE NOT EXISTS but I don't get it to work as intended.
I am a front-end person, so I am not all that knowledgeable about this. I hope that someone else knows if I was almost there but did something wrong or that I should make a whole other type of SQL script.
foreach ($values["assigned_users"] as $assigned_user) {
//tried this
(new \database\DataSource)->main()->query('REPLACE INTO task_users', [
'task_id' => (int) $values["id"],
'user_id' => $assigned_user
])
//and this
(new \database\DataSource)->main()->query('INSERT INTO task_users', [
'task_id' => (int) $values["id"],
'user_id' => $assigned_user
] , 'WHERE', [
'task_id' => (int) $values["id"],
'user_id NOT IN' => $values["assigned_users"]
])
//and this
(new \database\DataSource)->main()->query('INSERT INTO task_users', [
'task_id' => (int) $values["id"],
'user_id' => $assigned_user
] , 'SELECT * FROM task_users WHERE NOT EXISTS', [
'task_id' => (int) $values["id"],
'user_id' => $assigned_user
])
}
The script is PHP and uses the Nette Database query builder functionality
idcolumn in the link table. You can remove it and put the primary key on(task_id, user_id).