Saving multiple rows in a single query

Question

Is there anyway for the cake to do multi-line insertion in one query without writing raw SQL for this? The saveMany and saveAssociated parameters save only a few lines in one transaction, but this transaction contains several insert statements, so these methods are clearly not a solution for writing heavy applications.

Thank you for reading.

Answer 1

Someone pointed out to me the behavior of big data https://github.com/jmillerdesign/CakePHP_Big_Data

Answer 2

Yes

Although this is not a common practice in the application code, and this makes it possible to use almost any application logic (verification rules, behavior, events, etc.). You can see an example of this: the fixtures are loading :

$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 can also find this repository useful (either directly or as the basis for your code) that uploads fastener files to your application database - using multi-inserts.

Answer 3

Yes, Big_Data is a good idea to insert a large number. But as AD7six notes, it still uses basic quoting of values ​​and does not return insert identifiers. And based on your ideas, I wrote a small script to insert a large number into a single request, using CakePHP by default and returning the IDs of the 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; 

Answer 4

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

If you are using CakePHP 2, you will need to use raw SQL as follows:

 $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: Insert multiple lines using CakePHP 3

Answer 5

yes you can use as 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 post the method. you need to create the SQL manually to insert multiple rows at a time.

Please let me know if I can help you.