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?

Anthodium

Answer 1

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

The first argument is the values to insert/update and the second argument is the column(s) that uniquely identify records. All databases except SQL Server require these columns to have a PRIMARY or UNIQUE index.

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

Pape

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

How can we get the inserted ids? – Yager

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

Will it only work when there is a unique index applied to the column? @Pape – Slogan

Answer 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.

Genesis

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. – Willowwillowy

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

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

Answer 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 = [])

Furuncle