How to use insert in query builder to insert multiple records?

Question

I am trying to do the following in CakePHP 3:

$newUsers = [ [ 'username' => 'Felicia', 'age' => 27, ], [ 'username' => 'Timmy', 'age' => 71, ], ]; $insertQuery = $this->Users->query(); $insertQuery->insert(array_keys($newUsers[0])) ->values($newUsers) ->execute(); 

I get the following error:

 Error: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 SQL Query: INSERT INTO users (username, age) VALUES (:c0, :c1, :c2, :c3) 

I expected INSERT INTO users (username, age) VALUES (:c0, :c1), (:c2, :c3); as a request.

I turned on the log for database configuration and I see:

 2014-10-27 16:10:26 Debug: INSERT INTO users (username, age) VALUES (NULL, NULL, 'Array', 'Array') 

Please help me understand if I understand the potential of using the query builder in CakePHP 3.x

Answer 1

Just tested. It works.

  $newUsers = [ [ 'username' => 'Felicia', 'age' => 27, ], [ 'username' => 'Timmy', 'age' => 71, ], ]; $columns = array_keys($newUsers[0]); 

A warning! There are two ways to do bulk insertion. This is one way:

  $insertQuery = $this->Users->query(); $newUsersValuesExpression = new ValuesExpression($columns, $insertQuery->typeMap()->types([])); $newUsersValuesExpression->values($newUsers); $insertQuery->insert($columns) ->values($newUsersValuesExpression) ->execute(); 

As suggested by ndm, I prefer this way.

  $insertQuery = $this->Users->query(); $insertQuery->insert($columns); // you must always alter the values clause AFTER insert $insertQuery->clause('values')->values($newUsers); $insertQuery->execute(); 

Answer 2

You can combine several calls to value() :

 // ... foreach($newUsers as $values) { $query->values($values); } $query->execute(); 

or directly modify the ValuesExpression object (accessible through Query::clause() ), which has values() , which allows you to immediately set all the data:

 // ... $query->clause('values')->values($newUsers); $query->execute();