Update one-to-many relation entries without touching unaffected rows

Question

We have two models "Foo" and "Bar" like in the picture below (one Foo can have many Bars).

Let's say that we want to update Foo model with values from $_POST. Because of Foo relation with Bar, we also want to update Bar model with values from same $_POST. Foo updating process is the same as usual (i.e. load Foo model from database by foo ID, then load $_POST to Foo model and save the model). But Foo can have many Bars, thats why we delete all entries from bar table that have $fooId and create new entries to bar table from $_POST.

For example, user opens form where he can change Foo name and add/change/remove many bar names. Let's say current foo name is "foo" and current bars are "bar1", "bar2" and "bar3". User changes foo name to "fooChanged" and at the same time changes "bar1" to "bar10" and deletes "bar3". NOTE: that "bar2" was not touched. After submitting the form, controller loads Foo model, loads to it foo changes (now "foo" has been changed to "fooChanged") and saves the model. With Bar model it's a little bit different. Firstly, controller removes all entries, that have $fooId, and creates new ones with batchInsert (see the code below).

Controller:

public function actionUpdateFoo($fooId = null)
{
    $foo = Foo::findOne($fooId);
    $foo->load(Yii::$app->request->post());

    $transaction = Yii::$app->db->beginTransaction();
    if ($foo->save() && Bar::deleteAll(['foo_id' => $fooId]) && Bar::create($fooId, Yii::$app->request->post())) {
        $transaction->commit();
    } else {
        $transaction->rollBack();
    }

    return $this->render('foo', [
        'foo' => $foo,
    ]);
}

Bar model:

public static function create($fooId, $post)
{
    $array = [];
    foreach ($post['Bar'] as $item) {
        array_push($array, [
            'foo_id' => $fooId,
            'name' => $item['name'],
        ]);
    }

    return Yii::$app->db->createCommand()->batchInsert(self::tableName(), ['foo_id', 'name'], $array)->execute();
}

The problem we face is that in order to update many Bar entries, we must remove the old ones and add new ones. We think this approach is not optimal because if we have a lot of entries and user changes just one, we have to delete all of them and insert again the same ones plus updated one. (Like in example above, all three Bar entries will be removed even though "bar2" was not touched).

Is there any other better approach than this one (we want to ignore unchanged rows and only change the affected rows)?

You might look into deleting only Bar that are not in the $_POST submitted. Then after that do an insert ignore or replace into to add only new bars. — xangxiong, May 15 '17 at 15:00
Assuming you are getting related $foo data with $bar, when you fetch the $foo by id for update, create a compare algorithm that looks at the $_POST array against what is in your $foo related $bar data. Then you will know what to ignore and what to update. — dataskills, May 15 '17 at 15:42
I would track the id(s) ... add hidden ID field which would be posted when the Bar entries get submitted, compare with DB and if any change then updates those entries. Helps? — Hmmm, May 16 '17 at 08:28

score 2 · Accepted Answer · answered May 15 '17 at 21:03

It is not necessary to delete all rows first and then add again. We are using a simple method that detects changes and updates only updated rows. While this might not reduce the amount of lines written in code, but it reduces the amount of queries used which could improve loading speed.

Short summary for actionUpdateFoo($fooId = null):

We are loading Foo with new values. We are also selecting all Bars that are assigned to Foo model. Using foreach() we iterate through Bar and place ID of each found row to one variable ($dependantBars). Using method, we (always) get an array with a size of 2 (first element is an array of old values and second element is an array of new values). In if() we save updated Foo model and also check if deletions and insertions were successful.

/**
 * Let's say, we have in this example:
 * $dependantBars = [0, 1, 2, 3]; (old choices)
 * $foo['choices'] = [0, 1, 5, 7]; (loaded from Yii::$app->request->post()['Foo']['choices'])
 */
public function actionUpdateFoo($fooId = null)
{
    $foo = Foo::findOne($fooId);
    $foo->load(Yii::$app->request->post());

    $subFoo = Bar::findAll($fooId);
    $dependantBars = [];
    foreach ($subFoo as $foo) {
        $dependantBars[] = $foo->id;
    }

    $distinction = self::getDifferencesInArrays($dependantBars, $foo['choices']);

    $transaction = Yii::$app->db->beginTransaction();
    if ($foo->save() && Bar::updateUserChoices($distinction)) {
        $transaction->commit();
    } else {
        $transaction->rollBack();
    }

    // Do something else
}

Separate method in controller to get differences:

/**
 * Checks the difference between 2 arrays.
 *
 * @param array $array1 Old values that are still saved in database.
 * @param array $array2 New values that are selected by user.
 * @return array
 */
public static function getDifferencesInArrays($array1 = [], $array2 = [])
{
    return [
        array_diff($array1, $array2),
        array_diff($array2, $array1),
    ];
}

And in Bar class we can write this method to do both things in the same method (delete and insert):

public static function updateUserChoices($distinction)
{
    $deletedRows = true;
    $insertedRows = true;

    if (!empty($distinction[0])) {
        $deletedRows = self::deleteAll(['foo_id' => $distinction[0]]);
    }

    if (!empty($distinction[1])) {
        $insertedRows = Bar::create(); // Something here
    }

    return $deletedRows && $insertedRows;
}

Update one-to-many relation entries without touching unaffected rows

1 Answers1