Yii: save multiple records in one query

Question

I'm trying to save a lot of CActiveRecord model objects in a loop. I have something like this:

foreach ($array_of_items as $item) {

    $values = array(
        "title"   => $item->title,
        "content" => $item->content,
    );

    $object = new MyModel;
    $object->attributes = $values;
    $object->save();

}

In my case, this creates about 400 CActiveRecord objects. The saving process is really slow, because each save() queries the database.

Is there a way to save all those objects in one go? Something like:

$objects = array();

foreach ($array_of_items as $item) {

    $values = array(
        "title"   => $item->title,
        "content" => $item->content,
    );

    $object = new MyModel;
    $object->attributes = $values;
    $objects[] = $object;
}

save_all_objects($objects);

I could not find anything on the subject. Anyone?

no there is nothing to do that in Yii from the amount of time I have spent on it, the way you are doing it right now should be the only option in all probability - interested myself to know if there are alternates though. — Rohan, Jan 22 '14 at 19:37
I think you can find your solution in the accepted answer of [this question](http://stackoverflow.com/questions/18518001/batch-insert-in-yii). — nielsdg, Jan 22 '14 at 20:10
Ok, turns out all i really needed was to use transaction. Saving 400 models in foreach loop: 25 seconds. Wrapping the foreach loop in beginTransaction & commit: 0.36 seconds. — Olav Kokovkin, Jan 24 '14 at 17:42
http://www.yiiframework.com/doc/api/1.1/CDbCommandBuilder#createMultipleInsertCommand-detail — jamband, Feb 22 '14 at 23:34

score 4 · Accepted Answer · answered Jan 23 '14 at 05:19

you can validate() your model, and if it was ok you can append it so a sql text for insert,

and after your loop, just use databases commandBuilder() and execute your prepared text

$sql = '';
if($object->validate())
{
    $sql .= ',("' . $object->attr1 . '")'// append to script,(you get the idea, you need to also make a correct values)
}

...

if(!empty($sql))
{
    $sql = 'INSERT INTO table (attr1) Values' . $sql;// make complete script
    // execute that command
}

Nabi K.A.Z. · Answer 2 · 2018-12-18T00:51:39.643

For insert multi rows, Put this code in components folder under GeneralRepository.php file name.

<?php
class GeneralRepository
{
    /**
     * Creates and executes an INSERT SQL statement for several rows.
     * By: Nabi K.A.Z. <www.nabi.ir>
     * Version: 0.1.0
     * License: BSD3
     * 
     * Usage:
     * $rows = array(
     *      array('id' => 1, 'name' => 'John'),
     *      array('id' => 2, 'name' => 'Mark')
     * );
     * GeneralRepository::insertSeveral(User::model()->tableName(), $rows);
     * 
     * @param string $table the table that new rows will be inserted into.
     * @param array $array_columns the array of column datas array(array(name=>value,...),...) to be inserted into the table.
     * @return integer number of rows affected by the execution.
     */
    public static function insertSeveral($table, $array_columns)
    {
        $connection = Yii::app()->db;
        $sql = '';
        $params = array();
        $i = 0;
        foreach ($array_columns as $columns) {
            $names = array();
            $placeholders = array();
            foreach ($columns as $name => $value) {
                if (!$i) {
                    $names[] = $connection->quoteColumnName($name);
                }
                if ($value instanceof CDbExpression) {
                    $placeholders[] = $value->expression;
                    foreach ($value->params as $n => $v)
                        $params[$n] = $v;
                } else {
                    $placeholders[] = ':' . $name . $i;
                    $params[':' . $name . $i] = $value;
                }
            }
            if (!$i) {
                $sql = 'INSERT INTO ' . $connection->quoteTableName($table)
                . ' (' . implode(', ', $names) . ') VALUES ('
                . implode(', ', $placeholders) . ')';
            } else {
                $sql .= ',(' . implode(', ', $placeholders) . ')';
            }
            $i++;
        }
        $command = Yii::app()->db->createCommand($sql);
        return $command->execute($params);
    }
}

And usage anywhere:

$rows = array(
    array('id' => 1, 'name' => 'John'),
    array('id' => 2, 'name' => 'Mark')
);
GeneralRepository::insertSeveral(User::model()->tableName(), $rows);

https://www.yiiframework.com/extension/yii-insert-multi-rows

@AwaisUsmani It is interesting. That still yii version 1 has user! ;-) https://www.yiiframework.com/extension/yii-insert-multi-rows — Nabi K.A.Z., Dec 18 '18 at 00:50

score 1 · Answer 3 · edited Dec 17 '15 at 08:23

1

Since v1.1.14, the method createMultipleInsertCommand() of CDbCommandBuilder class is available.

edited Dec 17 '15 at 08:23

Damian Kozlak

7,065
10
45
51

answered Dec 17 '15 at 08:17

nurik_6

11
1

Yii: save multiple records in one query

3 Answers3

Linked