ActiveRecord batch insert (yii2) [closed]

Question

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?

Answer 1

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.