Hello caiosalchesttes,

There is an example of a regular sql statement that has an auto increment row

INSERT INTO `posts` (`id`, `text`) VALUES (NULL, 'I am Akpevwe Jenkins.');
 

If you notice, the value for the 'id' here is NULL because it is an auto-incremented value. I think if you provide null for the 'id' column in the second array, Laravel will simply ignore the null value and rely on the database to handle the auto-incrementing behavior for the 'id' column as usual. So, your code will look like this:

DatasetLabel::upsert(
    [
        ['id' => 4, 'name' => 'A', 'tenant_id' => 1, 'dataset_id' => 1],
        ['id' => null, 'name' => 'B', 'tenant_id' => 1, 'dataset_id' => 1]
    ],
    uniqueBy: ['id'],
    update: ['name']
);

Do let me know if it worked. All the best.

So when I do this, I get the following error:

SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation "dataset_labels" violates not-null constraint DETAIL: Failing row contains (null, 1, B, null, 0, 1, null, 2024-05-25 19:33:41, 2024-05-25 19:33:41).

Since it doesn't accept the null table, the only way would be to create a random ID, but I believe that this upsert function wouldn't be fulfilling the role assigned by Laravel. I think this would be an error, right?

Hello caiosalchesttes, I just tried it on my end , and it worked for me.

As for this error: Failing row contains (null, 1, B, null, 0, 1, null, 2024-05-25 19:33:41, 2024-05-25 19:33:41).

Please check your database if the ID colume is on auto increment.

on the other hand, it is advisable to have more than one uniqueBy colume Here is an example

<?php
//Tinker away!
use App\Models\User;
 User::upsert( [
    ['id' => 4, 'first_name' => 'c', 'last_name' => 1, 'email' => 'troy23@examplme.org', 'password'=>'akpevwe'],
    ['id' => null, 'first_name' => 'z', 'last_name' => 1, 'email' => 11, 'password'=>'akpevwe']
],
    uniqueBy: ['id','email'],
    update: ['first_name']);

@jenkins I’m running into an issue where it’s not working as expected in my case. Here’s the situation:

I attempted to use ChatQa::upsert with the following code:

ChatQa::upsert([
    ['id' => 7, 'chat_id'=> $chat->id, 'tenant_id' => 1, 'context' => 'oi123'],
    ['id' => null, 'chat_id'=> $chat->id, 'tenant_id' => 1, 'context' => 'oi']
], 
uniqueBy: ['id'], 
update: ['context']);

However, I’m getting the following error:

SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation > "chat_qas" violates not-null constraint DETAIL: Failing row contains (null, 1, 01j5ztqh6bp2tyvdvvea89pze1, oi, f, null, null, 2024-08-30 14:15:34, 2024-08-30 14:15:34, null, null).

From what I understand, this error occurs because the id column in my table is a primary key and does not allow null values. The upsert method seems to fail when trying to insert a new record where id is null, which makes sense because the id needs to be unique and non-null for primary key constraints.

So, it looks like upsert works well for updates when you already have an id but not when you’re trying to mix creation (with a null id) and updates in the same operation. To resolve this, it seems we have to separate the operations into:

Inserts for new records, ensuring that each new record has a valid, non-null id. Updates where the id is known and already exists in the database. If you have a workaround for this, I’d love to hear it!