CODEWITHSUNDEEP
cakephp
cnizzardini
cnizzardini

Reputation: 1240

Saving multiple rows in a single query

Is there anyway to have cake do a multi-row insert in a single query without writing raw SQL to do this? The saveMany and saveAssociated options will only save multiple rows in a single transaction, but that transaction contains multiple insert statements so these methods are clearly not a solution to write heavy applications.

Thanks for reading.

Upvotes: 7

Views: 3470

Answers (5)

null
null

Reputation: 4370

If you are using CakePHP 3.0 you can check the answer to this question: How to use insert in query builder insert multiple records?

If you are using CakePHP 2 you will have to use raw SQL like this:

$sql = "INSERT INTO `people` (`name`,`title`) VALUES ";
foreach($people as $person){
    list($name,$title) = $person;
    $sql.= "('$name','$title'),";
}
$this->query(substr($sql,0,-1));

Source: Inserting Multiple Rows with CakePHP 3

Upvotes: 0

user1836049
user1836049

Reputation:

Yes, Big_Data is good idea for inserting bulk. But as AD7six note, it still use basic value quoting and does not return insert ids. And base on your ideas, i wrote small script to inserting bulk in a single query, using default CakePHP quoting and returning ids of inserted records.

    $count = count($records);
    $dbSource = $this->getDataSource();
    $table = $dbSource->fullTableName($this->table);
    $fields = $dbSource->prepareFields($this, array('fields' => array_keys($records[0])));
    $values = array();
    foreach ($records as $index => $record) {
        if (!is_array($record) || !$record) {
            return null;
        }
        foreach ($record as $column => $value) {
            $values[$index][$column] = $dbSource->value($value, $this->getColumnType($column));
        }
        $values[$index] = '(' . implode(',', $values[$index]) . ')';
    }

    $query = 'INSERT INTO %s (%s) VALUES %s;';

    $query = sprintf($query, $table, implode(',', $fields), implode(',', $values));
    if (!$dbSource->execute($query)) {
        return false;
    }
    $lastInsertId = $dbSource->getConnection()->lastInsertId();
    $insertIds = array();
    for ($i = 0; $i < $count; $i++) {
        $insertIds[] = $lastInsertId + $i;
    }
    return $insertIds;

Upvotes: 2

cnizzardini
cnizzardini

Reputation: 1240

Someone pointed me towards the Big Data Behavior https://github.com/jmillerdesign/CakePHP_Big_Data

Upvotes: 0

AD7six
AD7six

Reputation: 66288

Yes

Though it's not a common practice to do so in app-land code, and doing so removes the possibility to use almost any application logic (validation rules, behaviors, events etc.). You can see an example of doing this in the way fixtures are loaded:

$db = ConnectionManager::getDataSource('default');

$table = "stuffs";
$fields = array('id', 'name');
$values = array(
    array(1, 'one'),
    array(2, 'two'),
    ...
);

$result = $db->insertMulti($table, $fields, $values);

You may also find this repository useful (either directly or as a basis for your code) which loads fixture files into your app database - using multi-inserts.

Upvotes: 3

liyakat
liyakat

Reputation: 11853

yes you can use like below

The getDataSource() method is static in CakePHP 2.x, so you should be able to use:

$db = ConnectionManager::getDataSource('default');
$db->rawQuery($some_sql);

here i am posting method to do. you have to create some SQL statement manually to insert multiple row in one time.

Please let me know if i can help you more.

Upvotes: -1

Related Questions