0

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

4
  • 4
    A simple way is to delete all the links for the task, then insert all the new ones. Commented Sep 17, 2024 at 14:38
  • 1
    Yep, just delete all the links and re-insert them, including any new/changed values and excluding any deleted ones. Much easier than messing about trying to map the changes onto existing records. Commented Sep 17, 2024 at 14:53
  • On a side note, you don't need the id column in the link table. You can remove it and put the primary key on (task_id, user_id). Commented Sep 17, 2024 at 14:59
  • FYI, this is a many-to-many relationship, not one-to-many. Commented Sep 17, 2024 at 15:18

1 Answer 1

-2

I think it is better to remove the id column and remove and insert the entries from the current task completely, as Olivier suggested.

After further consideration (and learning more about databases) I would not recommend this approach, since it is possible to lose data and does not follow the rules for database normalization

Sign up to request clarification or add additional context in comments.

2 Comments

Why repeat a comment as an answer?
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.