What is the right thing to do when saving batchInsert?

Question

Yii

I'm trying to organize the work of the catalog as follows: these are the Category, CatToProd, Product models, that is, the Product is connected through CatToProd with the Category. Moreover, products can have many categories. I save the categories of the product as follows: this is the view connected through the controller with the product model

<?=$form->field($model, 'category')->widget(Select2::classname(), [
            'data' => $category,
            'language' => 'en',
            'options' => ['placeholder' => Html::encode('Select Category'),'multiple' => true],
            'pluginOptions' => [
                'allowClear' => true
            ],
        ])->label(false); ?>// $category массив ключ id категории и title

thus trying to save in the model

public function  afterSave($insert, $changedAttributes)
    {
        $this->categorySave($this->category,$this->id);
        parent::afterSave($insert, $changedAttributes); // TODO: Change the autogenerated stub
    }
    protected function categorySave($categorysId,$modelId){
        $batchArr=array();
        foreach ($categorysId as $category){
            $batchArr[]=[$category,$modelId];
        }
        Yii::$app->db->createCommand()->batchInsert('cat_to_prod',['id_category','id_product'],$batchArr)->execute();
    }

but here, as they say, unexpected paraska won Ukrainian football players. What is the point when editing a product, if I try to edit categories, new ones are recorded, and if I want to delete old ones, they are not deleted. I can't even imagine the best way to organize it. Can you advise where to go.

Answer 1

Well, they won't be deleted. You don't delete them, you just add them.
For myself, I have identified two approaches for such cases:
1) Clean but difficult. We take a list of product categories from the database and use array_diff to compare it twice with what came from the form. Once array_diff($cats_from_form, $cats_from_db), the second time we swap the arguments. This function returns the elements of the second array that are not in the first one or something like that, I don't remember exactly. Thus, we get a list of categories that the user has added and they need to be written to the database, and a list of categories that the user has removed - and they need to be deleted. Then we just do a batchInsert on one array and divides on another.
2) Dirty but fast. We simply delete all product categories and reinsert all categories that came from the form.
In fact, it makes sense to use the first method only if the record of the product's relationship with the category "valuable" and stores something other than foreign keys to these two tables.