How to use insert in query builder insert multiple records?

Question

I am trying 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 was expecting INSERT INTO users (username, age) VALUES (:c0, :c1), (:c2, :c3); as the query.

I turned on the log for the database config 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 misunderstood the potential of using query builder in CakePHP 3.x

Kim Stacks · Accepted Answer · 2014-10-28 07:43:48Z

4

Just tested. This works.

   $newUsers = [
        [
            'username' => 'Felicia',
            'age' => 27,
        ],
        [
            'username' => 'Timmy',
            'age' => 71,
        ],
    ];

    $columns = array_keys($newUsers[0]);

Alert! There are two ways to do mass insert. 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();

edited Oct 28, 2014 at 7:43

answered Oct 27, 2014 at 16:58

Kim Stacks

10.9k39 gold badges168 silver badges302 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

asmmahmud Over a year ago

Your second method has worked for me in Cakephp 3.0

vikash Over a year ago

@kim - how I can get the all inserted data after query execution? Like this insertion method returning the inserted data $this->saveMany($this->newEntities($inser_data));

ndm · Accepted Answer · 2014-10-27 16:34:07Z

3

+100

You can either combine multiple value() calls:

// ...

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

or modify the ValuesExpression object directly (accessible via Query::clause()), which has a values() method that allows to set all data at once:

// ...

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

edited Oct 27, 2014 at 16:34

answered Oct 27, 2014 at 16:25

ndm

60.6k9 gold badges73 silver badges113 bronze badges

10 Comments

Kim Stacks Over a year ago

I think you did not phrase it exactly correct, but thank you! You did point to me another way to derive the ValuesExpression

ndm Over a year ago

@KimStacks What do you think was phrased wrong? The missing fact that values need to be applied after invoking insert()? That's generally how it has to be done. Also note that it's not necessary to reapply the ValuesExpression instance.

Kim Stacks Over a year ago

Oops sorry you were right. I will change my answer. Man, you are good. How long you have been using Cake3?

ndm Over a year ago

@KimStacks I'm not really using (as in using it in an actual project) CakePHP 3 yet, I'm justing testing specific things here and there, however I've been following the development since it's beginning, I'm watching all the issues and pull requests on GitHub, ocassionally contributing code and fixes, and last but not least I'm trying to solve problems by people here on SO (when I should actually be working of course), that's my way of becoming acquainted with code. And no, I don't have a blog, my public activity is limited to SO and GitHub.

ndm Over a year ago

@Techbrunch stackoverflow.com/questions/17035559/…

|

Collectives™ on Stack Overflow

How to use insert in query builder insert multiple records?

2 Answers 2

2 Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related