Laravel upsert operations with Query Builder

Question

In one of the my worker scripts to store aggregate counts based on some metrics, I am not using Eloquent as the queries are a little complex and and it is easy to write using query builder. I am currently getting the values from the database and I need to insert/update it in the database. Can an upsert operation is possible by using the save() method can be achieved using Query Builder? Or do I need to check every time whether this entry is in database?

I have a total of 100,000 entries and would like to run it as a daily job. So if I need to check whether a particular entry is there in DB, I need to access the database that many times. Is there an alternative solution for this ?

I am thinking about creating two model classes, one using Eloquent and one using query builder. Can I use my custom query in the Eloquent model?

Solution 1

Now (Oct 6, 2020), Laravel(v8.10.0) has native upsert support. https://github.com/laravel/framework/pull/34698

DB::table('users')->upsert([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
], 'email');

Solution 2

Eloquent has updateOrCreate() method which allows you to do exactly want you want. But Query Builder doesn't have similar method.

You can build raw query with Query Builder and use INSERT ... ON DUPLICATE KEY UPDATE as upsert solution.

Solution 3

Eloquent has the method called updateOrCreate, which can be used like this example:

<?
$flight = Flight::updateOrCreate(
    [
       'code' => '156787', 
       'destination' => 'COL'
    ],
    [
       'code' => '156787', 
       'destination' => 'COL',
       'price' => 99, 
       'discounted' => 1,
    ],
);

1. Search by code and destination could by your best to identify your rows.
2. It creates or updates according to the values given in the second array.

The following is the sign of the method.

/**
 * Create or update a record matching the attributes, and fill it with values.
 *
 * @param  array  $attributes
 * @param  array  $values
 * @return \Illuminate\Database\Eloquent\Model
 */
public function updateOrCreate(array $attributes, array $values = [])

Comments

[Alex Blex]

Be aware updateOrCreate is not as atomic as SQL upsert: github.com/illuminate/database/blob/…. It selects first, then insert or update, which can be prone to race conditions.

[Boyd Hemphill]

Note: Insert ... on duplicate key ... is MySQL Syntax. Other RDBMS will have similar functionality but with different syntax. Postgres for example

[Jonas Staudenmeir]

I've created an UPSERT package for all databases: github.com/staudenmeir/laravel-upsert

[Happy Coder]

Thanks for checking this. I have asked the question 4 years before.

[Sumit Wadhwa]

How can we get the inserted ids?

[Lakmal Premaratne]

@HappyCoder No matter how old the question is if you have something to say. It would help someone who stumbled upon your question.