ActiveRecord batch insert (yii2)

53.2k views Asked by At

Is it possible to insert multiple rows in one query with Yii's ActiveRecord? Or is this only possible via the lower-level DAO objects?

I have two models 1- Transaction 2-TransactionItems

There are multiple rows(onclick add row) in transaction Items.

I want to store multiple rows of transactionitems in the database.

Screenshot of Transaction item table

1

There are 1 answers

9
arogachev On BEST ANSWER

You can use batchInsert() method of yii\db\Command. See details here. When using it with ActiveRecord make sure validate all data before inserting.

Assuming you have array of $models with class Post, it can be done like this:

$rows = [];
foreach ($models as $model) {
    if (!$model->validate()) {
        // At least one model has invalid data

        break;
    }

    $rows[] = $model->attributes;
}

If models don't require validation you can short the code above using ArrayHelper for building $rows array.

use yii\helpers\ArrayHelper;

$rows = ArrayHelper::getColumn($models, 'attributes');

Then simply execute batch insert:

$postModel = new Post;

Yii::$app->db->createCommand()->batchInsert(Post::tableName(), $postModel->attributes(), $rows)->execute();

P.S. The $postModel just used for pulling attirubute names list, you can also pull this from any existing $model in your $models array.

If you don't need to insert all attributes you can specify it when filling $rows array:

$rows[] = [
    'title' => $model->title,
    'content' => $model->content,
];

Don't forget to replace $postModel->attributes to ['title', 'content'].

In case of larger amount of attributes you can use some array functions to specify exact attributes for inserting.