How to update/insert array of values to database using Yii2 ( many values for one id that repeats )

Question

I am collecting some data from database and then display it in index view. There I have check box near every row, so I can allow users to pick their favorites.

Picture and text is selected from one table cpv. That table has fields: id, title, image. Since logged in user can pick up to 3 of the entries as favorites, I am having table to store relation between user and cpv.id he chose. user_cpv table has columns: id, user_id, cpv_id.

I made some dirty way of displaying data + checkboxes, and I made some way of passing information ( cpv.id ) to the actionUpdate that should save cpv.id's. But I can not figure out how to save all of this to user_cpv table. Can someone give me some idea about how to do this properly and how to do validation that user can not select more than 3 boxes? Here is my code:

index view :

<?php foreach ($values as $data): ?>
    <tr>
        <td>Img goes here</td>
        <td>Title goes here</td>

        <?php // here I have some dirty code, for each row displayed I am executing query that should find entry in 
              // user_cpv table where cpv_id there is == with the cpv.id taken drom cpv table.
              // can this be done more efficient ?
         ?>

        <?php $cpv = UserCpv::getCpvByCpvId($data['cpvId']) ?>
        <?php if ($cpv): ?>
            <td><?= Html::checkbox('favorite[]', true, ['value' => $data['cpvId']]) ?></td>
        <?php else: ?>
            <td><?= Html::checkbox('favorite[]', false, ['value' => $data['cpvId']]) ?></td>
        <?php endif ?>

    </tr>
<?php endforeach ?>

Form is opened with Html::form:

My actionUpdate:

public function actionUpdate()
{
    $userId = Yii::$app->user->identity->id;

    // array of checked cpv.ids (['0' => someId, ['1' => otherCheckedId]])
    $favorites = Yii::$app->request->post('favorite');

    $model = UserCpv::findOne(['user_id' => $userId]);

    if ($model) 
    {
        // this does not work
        $update = UserCpv::updateAll(['cpv_id' => $favorites], "user_id = $userId");

        return $this->render('index', [
            'model' => $model
        ]);
    } 
    else 
    {
        # code...
    }
    // ???
}

Do anyone have any idea how this should be done properly ?

Answer 1

I see two ways: first one is to create 'batchUpdate' method - but it will not be the same for different database types. So I will describe second one, as it seems to be pretty simple.

1. Remove all user relations: 

    UserCpv::deleteAll(['user_id' => $userId]);

2. Create array for batchInsert method:

    $data = [];
    foreach($favorites as $cpv_id) {
        $data[] = ['cpv_id' => $cpv_id, 'user_id' => $userId];
    }

3. Batch insert your data

    Yii::$app->db->createCommand()->batchInsert(
        UserCpv::tableName(), 
        ['cpv_id', 'user_id'], 
        $data
    )->execute();