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?

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

Extranuclear

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.

Antonelli

So, the answer is no? I should use DAO. – Extranuclear

I think currently the ActiveRecord does not support this out of the box. You can do some further research, but I can't find it. – Antonelli

$postModel->attributes should be $postModel->attributes() – Moule

@Moule Thanks for remark, corrected the answer. – Antonelli

@arogachev: how can get all laster insert id in batch insert ? – Decalescence

Extended version of this approach (that supports beforeSave and afterSave events) can be found here: https://mcmap.net/q/426461/-yii2-batch-insert-with-activerecord – Admission

works well; you must consider that it gives sql error if data array happens to be empty – Jehanna

is the custom behaviors() on that Post model stil working? In my case it didn't. – Epicarp

what if I want to skip any column from $rows? – Inconsistent