How to implement INSERT ON DUPLICATE KEY UPDATE aka upsert in CakePHP 3?

Question

I am using CakePHP 3 and MySQL.

I would like to implement an INSERT on DUPLICATE KEY UPDATE aka upsert query through the CakePHP 3 model.

Given the following table:

 +----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | admin | 33 | | 2 | Timmy | 17 | | 3 | Sally | 23 | +----+----------+-----+ 

where id is the primary key and username is a unique index

When I have the following values ​​pending update:

 Felicia, 27 Timmy, 71 

I expect the following result after upsert:

 +----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | admin | 33 | | 2 | Timmy | 71 | | 3 | Sally | 23 | | 4 | Felicia | 27 | +----+----------+-----+ 

I know how to do upsert in a MySQL query:

 INSERT INTO `users` (`username`, `age`) VALUES ('Felicia', 27), ('Timmy', 71) ON DUPLICATE KEY UPDATE `username`=VALUES(`username`),`age`=VALUES(`age`); 

I know how to do this in more than one request in CakePHP3.

  $newUsers = [ [ 'username' => 'Felicia', 'age' => 27, ], [ 'username' => 'Timmy', 'age' => 71, ], ]; foreach ($newUsers as $newUser) { $existingRecord = $this->Users->find() ->select(['id']) ->where(['username' => $newUser['username']]) ->first(); if (empty($existingRecord)) { $insertQuery = $this->Users->query(); $insertQuery->insert(array_keys($newUser)) ->values($newUser) ->execute(); } else { $updateQuery = $this->Users->query(); $updateQuery->update() ->set($newUser) ->where(['id' => $existingRecord->id]) ->execute(); } } 

What I want to know:

Is there a way to do upsert using CakePHP 3 on the same line, even if I use chaining?

Please advise how to implement this.

Answer 1

Using the answer fooobar.com/questions/972534 / ... , I want to rephrase this using the sample code provided in the question.

To download the following entries

 Felicia, 27 Timmy, 71 

to this table

 +----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | admin | 33 | | 2 | Timmy | 17 | | 3 | Sally | 23 | +----+----------+-----+ 

the best way is to write it as

 $newUsers = [ [ 'username' => 'Felicia', 'age' => 27, ], [ 'username' => 'Timmy', 'age' => 71, ], ]; $columns = array_keys($newUsers[0]); $upsertQuery = $this->Users->query(); $upsertQuery->insert($columns); // need to run clause('values') AFTER insert() $upsertQuery->clause('values')->values($newUsers); $upsertQuery->epilog('ON DUPLICATE KEY UPDATE `username`=VALUES(`username`), `age`=VALUES(`age`)') ->execute(); 

Check this epilog for more information on epilog

Check this box for details on how to write inserts of multiple records in a single query.