staudenmeir / laravel-upsert Goto Github PK

DB::table('stats')->upsert( [ ['post_id' => 1, 'date' => now()->toDateString(), 'views' => 1], ['post_id' => 2, 'date' => now()->toDateString(), 'views' => 1], ], ['post_id', 'date'], ['views' => DB::raw('stats.views +'.$post_id)] );

how access post_id when update?

Hi,

Thanks for the awesome package. Is it possible to only update a value if it is empty in the updated record?

For example, I have a database with products that have an "image" column. I am using upsert to quickly insert new or update existing products. When a product already exists in the database, the update is fired. With this, is there an easy way to check if the database product already has a value in image, and if now, update it?

Hi @staudenmeir,

I have looked a bit at the code, and there is something I don't understand:
in Builder.php there is this code:

if (!is_array(reset($values))) {
    $values = [$values];
}

(note that $values parameter is typehinted as array scalar)

The is_array() test seems superfluous (it's forcibly an array),
and why the reset()?

May you explain this to me? Thanks :)

I get this when I try to install it:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - staudenmeir/laravel-upsert[v1.0, ..., 1.0.x-dev] require illuminate/database 5.5.*|5.6.*|5.7.* -> found illuminate/database[v5.5.0, ..., 5.7.x-dev] but these were not loaded, likely because it conflicts with another require.
    - staudenmeir/laravel-upsert[v1.1, ..., 1.1.x-dev] require php ^7.1.3 -> your php version (8.0.7) does not satisfy that requirement.
    - staudenmeir/laravel-upsert v1.2 requires php ^7.2 -> your php version (8.0.7) does not satisfy that requirement.
    - staudenmeir/laravel-upsert v1.3 requires php ^7.2.5 -> your php version (8.0.7) does not satisfy that requirement.
    - staudenmeir/laravel-upsert v1.4 requires php ^7.3 -> your php version (8.0.7) does not satisfy that requirement.
    - Root composer.json requires staudenmeir/laravel-upsert ^1.0 -> satisfiable by staudenmeir/laravel-upsert[v1.0, ..., v1.4].

Is there really a conflict for PHP 8 or can the requriements be changed?

Hello there,

I wonder if there is any issue with laravel-upsert and Laravel 8 right now, since I ran into this issue while upgrading from 7 to 8:
Declaration of Staudenmeir\LaravelUpsert\Query\Builder::upsert(array $values, $uniqueBy, ?array $update = NULL) should be compatible with Illuminate\Database\Query\Builder::upsert(array $values, $uniqueBy, $update = NULL)

For me it looks like that the definitions just dont match:
`
// laravel-upsert/src/Query/Builder.php

/**
 * Insert new records or update the existing ones.
 *
 * @param array $values
 * @param array|string $target
 * @param array|null $update
 * @return int
 */
public function upsert(array $values, $uniqueBy, array $update = null)`

`
// laravel/framework/src/Illuminate/Database/Query/Builder.php

/**
 * Insert new records or update the existing ones.
 *
 * @param  array  $values
 * @param  array|string  $uniqueBy
 * @param  array|null  $update
 * @return int
 */
public function upsert(array $values, $uniqueBy, $update = null)`

The problem is the "array" type definition it seems.
I see that there is a commit to upgrade to Laravel 8, so my question is if this is an issue on my particular project or it is missing in laravel-upsert?

If you would like to have a PR from me, I could help out.

Best regards, Martin

When I call ::upsert() against a model class it returns an Eloquent Builder instead of an integer of affected rows. This contradicts the documented return type. Is this a bug?

If I use DB::table(…) instead of the model then it returns the int.

I need to get id from inserted data or updated data, is there a way to get it after ran upsert? Thanks

I am using a cache package for the models, but wanting to use your package with eloquent models tells me collision error, it is because your package cancels newEloquentBuilder (). Is there any way to fix it? thanks.

cache pack I am using:
https://github.com/GeneaLabs/laravel-model-caching

Hi friends,

Is is possible that I insert or Update multiple rows in one attempt with different values?.

I see on documentation it is possible for using multiple row insert/update with the same value.

Please let me know what is best practice using this for gaining more speed and decrease query counts ?

Illuminate\Database\QueryException : SQLSTATE[21000]: Cardinality violation: 7 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

I have array with some items, and I try add all this items in DB. And may be there are more than one duplicate rows, it is normal case for my situation, script must always update it for setting last value from source (I use it in seeders). May be, I can configure PostgreSQL to fix it? Thanks.

It appears the Eloquent upsert() and insertIgnore() do not trigger model touch relationships for updating owner/parent timestamps as outlined here:

https://laraveldaily.com/eloquent-touch-for-models-and-their-relationships/

These are the relationships defined in the model as $touches.

This is a feature request to fire off events for observers methods such as saved, created, or updated. As it is one must manually do the work of the observers or explicitly trigger the events each time.

Ideally this could also work with preemptive observer methods like saving, creating, updating, etc. And it could also add a non-static upserting method like saveUpsert(array $update).

Would it be possible to have upsert and insertIgnore, return the model they inserted/updated?

First of all, a massive thank you @staudenmeir for this package.

I'm working on updating an application from Laravel 7 to 8. It was going well until 8.10 rolled around with its own implementation of upserting. Before 8.10, this just worked with Laravel 8. With 8.10, the method signatures of Laravel's upsert and laravel-upsert differ resulting in a fatal error.

I've removed laravel-upsert from my application and rewrote the upsert usages to reflect Laravel's signature. The resulting queries are identical as far as I can tell.

I'm wondering if there's anything in this package that is not in Laravel core? Potentially some edge cases I'm missing. Our database is running MySQL 5.7, code is Laravel 8.x, PHP 7.4.

Is it possible to add more examples so a Laravel newbie like myself could also use the library. For example what would be the best way to update relationships?

Category hasMany Product

Should I add the HasUpsertQueries trait to the Category or Product model to run the statement below?

category->products()->upsert([...])

I want to update or create products that belong to a specific category. How would I solve that?

Currently trying to use this package in combination with something like https://github.com/michaeldyrynda/laravel-efficient-uuid which obviously won't work because it relies on the eloquent model events which are not triggered with this package from what I can tell due to it executing raw queries.

What would be the recommended way of using UUIDs with this package? Since the UUID can't be automatically handled through events I would probably need to manually generate it the first time a record is created but since it uses an UPSERT without any additional queries I don't have the knowledge about an existing record that matches all data, excluding the UUID, to know if I should generate the UUID.

Could not find a matching version of package staudenmeir/laravel-upsert

Anyone else? I have ran the composer install text just as in the documentation.

Currently the number of affected rows are returned when using the query builder interface:

$affectedRows = DB::table('stats')->upsert(
    [
        ['post_id' => 1, 'date' => now()->toDateString(), 'views' => 1],
        ['post_id' => 2, 'date' => now()->toDateString(), 'views' => 1],
    ],
    ['post_id', 'date'],
    ['views' => DB::raw('stats.views + 1')]
);

However, the same is not true for the Eloquent model interface. I'm not sure if this is a defect or an enhancement.

Stat::upsert(
    [
        ['post_id' => 1, 'date' => now()->toDateString(), 'views' => 1],
        ['post_id' => 2, 'date' => now()->toDateString(), 'views' => 1],
    ],
    ['post_id', 'date'],
    ['views' => DB::raw('stats.views + 1')]
);

In Laravel v6.* we have to add the Staudenmeir\LaravelUpsert\DatabaseServiceProvider::class, provider in the config/app.php for the above issue. But in Laravel v7.* the same provider will be auto injected as the library's composer.json description stated--

Therefore, @staudenmeir please update the help docx as well. Thx!

It seems like that using upsert is not dispatching any observable events

When I use upsert in Eloquent, I always get value of 200, I thought it might be the total records inserted/updated, but my records are 100 only, I tried using 10 records, got the same value, 1 record, same value,
Can you advise what is that number? or at least is there a way to get the total number of the records that were affected?

hi ✋

PHP : 7.4
Laravel : 8.10.0
Sqlite : 3.22.0
Ubuntu : 18.04

i'm using your package but i have a problem regarding the sqlite version.

at the beginning, i had the version 3.22.0 of sqlite on my pc, that's why i have this error message :

SQLSTATE[HY000]: General error: 1 near "on": syntax error (SQL: insert into "offer_option_choices" ("condition_map", "created_at", "id", "offer_option_id", "updated_at", "value") values (my new condition, 2020-11-12 17:40:30, eb8fe6f5-8eb0-4b49-8e0d-26f7a06dc3e5, 91fe0a73-16d2-4c3d-9aaa-2505451cd619, 2020-11-12 17:40:30, my new value), (my updated condition, 2020-11-12 17:40:30, 91fe0a73-1760-40b8-8aec-3cfff23767b8, 91fe0a73-16d2-4c3d-9aaa-2505451cd619, 2020-11-12 17:40:30, my updated value) on conflict ("id") do update set "value" = "excluded"."value", "condition_map" = "excluded"."condition_map", "updated_at" = "excluded"."updated_at")

I know the minimum required version of sqlite is 3.24.0, for that, i upgraded it on my pc to the latest version 3.34.0, but on phpinfo() it still shows me the version 3.22.0 and it didn't want to change

I don't know how to update the sqlite.so php extension to the required version !

Receiving on Laravel ^7.0

Call to undefined method Illuminate\Database\Query\Builder::upsert()

I believe the issue is related to another package we are using: https://github.com/michaeldyrynda/laravel-efficient-uuid

In Staudenmeir\LaravelUpsert\Connectors\ConnectionFactory::createConnection() the custom connection is never used

Hi,
I didn't find a way to update timestamp field updated_at during Eloquent Model upsert ONLY IF a row has a field changed.
Laravel Models change the updated_at field if isDirty() = true.
You write into documentation that updated_at is added to Eloquent Models and it's true, but it always added and updated even also no field changes.

Do you have suggestions?
Thanks so much!

(quite some time passed since laravel/ideas#1090 …)

My use-case for RETURNING is mostly about getting back:

• the primary key of newly inserted rows
• or the primary key of the modified row (in case of DO UPDATE SET)

For DO NOTHING, RETURNING won't return anything for the non-INSERT case.

The goal is avoid having to run a separate query to know which rows where affected. I know I can run a separate query using an index (it's the base requirement for ON CONFLICT anyway), but I still rather avoid this for cases where a LOT of upserst / second can happen.

I've checked all my "upsert use-cases", and each of them takes advantage of this.

Would it in your opinion make sense to support this for PgSQL?

I don't use / can't comment on any support of this for other databases.

Looking at the sources in \Staudenmeir\LaravelUpsert\Query\Builder::upsert and \Staudenmeir\LaravelUpsert\Query\Builder::insertIgnore, it probably means:

• adding a new optional parameter for the columns to return (can be more than one)
• changing the signature of the method when such parameter is provdided

It would probably make more sense to introduce a new upsertReturning (and insertIgnoreReturning respectively), but I don't want to get ahead of things.

Thanks

The records are not saved in the table if the name of the table begins with a number, for example "1_table".
I forget to say that is in postgresql with laravel 6

Hi @staudenmeir,

Thank you for this wonderful addition to the Laravel framework. Currently we're using this package in October CMS (based on Laravel 6 LTS) and we would like to upgrade our project to PHP 8.0. Unfortunately version 1.2 of this package prevents us from upgrading. It would be great to have support for PHP 8.0 for the Laravel 6.0 version of this package.

Problem 1

• Root composer.json requires staudenmeir/laravel-upsert 1.2 -> satisfiable by staudenmeir/laravel-upsert[v1.2].
• staudenmeir/laravel-upsert v1.2 requires php ^7.2 -> your php version (8.0.14; overridden via config.platform, same as actual) does not satisfy that requirement.

Can this be used in laravel 5.1!

Call to undefined method Illuminate\\Database\\Query\\Builder::upsert()

Laravel ^6.2

Thanks for the awesome package!

InsertOrIgnore has already made it to the framework core. I'd suggest we could possibly add a PR for upsert into the core as well. The current updateOrInsert supports only a single record and a bulk upsert operation would be a great addition to the core imo!

Thoughts on Eloquent functions firing model events?