Support the ongoing development of Laravel.io →
posted 4 months ago
Laravel
0

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.

Last updated by @jenkins 4 months ago.
0

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?

0

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' => '[email protected]', 'password'=>'akpevwe'],
    ['id' => null, 'first_name' => 'z', 'last_name' => 1, 'email' => 11, 'password'=>'akpevwe']
],
    uniqueBy: ['id','email'],
    update: ['first_name']);
Last updated by @jenkins 4 months ago.
0

@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!

Last updated by @gustavo-neumann 1 month ago.

caiosalchesttes liked this reply

1

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.