Updating a many-to-many table

[NotionCommotion]

I think this is a PHP question, but if can be solved only with SQL, please move it.

 

In table m2m, I have the following records:

c1 c2
 1  1
 1  2
 1  3
 2  1
 2  5

The server just received a request to use c2 values 1, 3, and 4 for c1 value 1.  As such, table m2m should be updated to the following:

c1 c2
 1  1
 1  3
 1  4
 2  1
 2  5

How is this best accomplished?

[ginerjm]

First - there is no m-2-m table. A table is a table. TWO tables can have a m-2-m relationship, but not just one. So - what are you really talking about?

[Jacques1]

Remove all associations for the c1 value that should be updated, then insert all new associations.

[NotionCommotion]

Remove all associations for the c1 value that should be updated, then insert all new associations.

 

Thanks Jacques,

 

So, not the following?  Instead use array_diff() (or similar) first to find the deleted and new records, and just delete and add as applicable?

$stmt = $this->db->prepare("DELETE FROM m2m WHERE c1=?");
$stmt->execute([$c1]);
$data=[];
foreach($c2s as $c2) {
    $data[]=$c1;
    $data[]=$c2;
}
$sql="INSERT INTO m2m(c1,c2) VALUES ".rtrim(str_repeat('(?,?),',count($c2s)),',');
$stmt = $this->db->prepare($sql);
$stmt->execute($data);

 

[NotionCommotion]

First - there is no m-2-m table. A table is a table. TWO tables can have a m-2-m relationship, but not just one. So - what are you really talking about?

 

Do you really not know?

[Jacques1]

So, not the following?

 

That is what I'm suggesting. You first delete all old associations, and then you insert all new associations.

 

Note that your code is subject to race conditions. If two instances of the script run simultaneously, you can end up with a mixture of data.

Edited July 24, 2016 by Jacques1

[NotionCommotion]

My first script would delete the records even if they shouldn't be deleted, but then would insert them back.  The following (untested) only deletes records which should be deleted, and only adds records which are being added.  Which approach do you think is best?

 

Also, please describe where the race condition occurs, and what might be a solution to eliminate it.

 

Thanks

$sql="SELECT c2 FROM m2m WHERE c1=?";
$stmt = $this->db->prepare($sql);
$stmt->execute(array($pk));
$existing = $stmt->fetchAll(PDO::FETCH_COLUMN);

$deleted=array_diff($existing,$c2s);
$added=array_diff($c2s,$existing);

if($deleted) {
    $sql="DELETE FROM m2m WHERE c2 IN (".rtrim(str_repeat('(?,?),',count($deleted)),',').") AND c1=?";
    $stmt = $this->db->prepare($sql);
    $deleted[]=$pk;
    $stmt->execute($deleted);
}
if($added) {
    $data=[];
    foreach($added as $item) {
        $data[]=$pk;
        $data[]=$item;
    }
    $sql="INSERT INTO m2m(c1,c2) VALUES ".rtrim(str_repeat('(?,?),',count($added)),',');
    echo($this->showQuery($sql,$data));
    $stmt = $this->db->prepare($sql);
    $stmt->execute($data);
}

[kicken]

Generally when your updating associations like this, you'd just have a complete list of what records should be associated. Rather than try and take that list and compare it to the existing DB to decide what to add/remove, it's simpler to just remove everything then insert the necessary associations.

 

If for some reason you don't think deleting all the old associations will work for you, then explain why that won't work as it's generally uncommon.

 

Think of your many-to-many setup as just a one-to-many setup when it comes to updating it. If your updating the c1 side with new associations, it's like a one-to-many between the c1 value and all the c2 values. If your updating the c2 side, it's just the opposite.

[NotionCommotion]

Generally when your updating associations like this, you'd just have a complete list of what records should be associated. Rather than try and take that list and compare it to the existing DB to decide what to add/remove, it's simpler to just remove everything then insert the necessary associations.

 

If for some reason you don't think deleting all the old associations will work for you, then explain why that won't work as it's generally uncommon.

 

Hi Kicken,  I agree it is simpler just deleting everything and then adding the existing plus new records.  And I am happy to do so unless there are reasons why I shouldn't.  My application is not unique, and if it is generally a good idea to do so, then I should also do so.  Guess my concern was the microsecond of extra database processing, but it probably will be offset by the extra SELECT query and PHP logic processing of other solutions.  My  only other concern is some sort of quantum race condition which will never happen but keep me up at night.  Thanks