updateOrCreate update doesn't work

Hi team. I am using the updateOrCreate function to create or update a client in my DB. When I add a new client everything work well, but when I want to update I get the error message "SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 (SQL: update parents set street_address = 630 Haines Avenue Northwest, zip = 87102, phone_number = (672)-343-2578, client_type = Fee Paying, latitude = 35.10324800000001, longitude = -106.6520827, parents.updated_at = 2021-09-30 15:12:11 where id = 1)" , and this started when I added the location address into my table before it was working well. This is my code

$data = Parents::updateOrCreate(
              ['email' =>  $request->inputEmail],
              ['full_name' => $request->name, 'street_address' => $street, 'city'=>$request->city,'county'=>$request->county,    'state'=>$request->state, 'zip'=>$request->postal_code, 'phone_number' => $request->phone, 'client_status'=>$status, 'client_type'=>$request->type, 'assistance'=>$assistance, 'details'=>$request->inputDetails, 'latitude' => $request->latitude,  'longitude' => $request->longitude, 'created_by' =>$userid]
          );

Can you support on this. Thank you.

bugsysha

4 years ago

Level 61

First here is the reminder how it works: The updateOrCreate() method attempts to find a Model matching the constraints passed as the first parameter. If a matching Model is found, it will update the match with the attributes passed as the second parameter. If no matching Model is found a new Model will be created with both the constraints passed as the first parameter and the attributes passed as the second parameter.

You already have the message of what are you doing wrong:

Insert value list does not match column list

So you are either providing some array key with value for which you don't have a column in the database, or you are missing some key with value for the existing column.

cianekaj

4 years ago

Level 1

@bugsysha Yes that how it should behave. But I am getting the above error during the update yet the model is available; During a new client the record is being created without any error! If I am missing key values or I provide extra values shouldn't this affect even the insertion of a new record?

bugsysha

4 years ago

Level 61

@cianekaj yes, that should be the case, but I can't base my assessment on your code. I need to base it on the error you are getting.

AlexElementarteilchen

4 years ago

Level 9

Hi,

take a look at the $fillable property of the Parents model.

My guess is that not all attributes are fillable and updateOrCreate therefor does not update these fields.

If you compare the field list from the SQL error message with the list of variables you are passing to updateOrCreate you'll see, that there are some missing.

(Yes, I did spend 2h recently with a very similar thing in my code :-))

Hope this helps!

1 like

cianekaj

4 years ago

Level 1

@AlexElementarteilchen Hi Alex, I thought of this too at the beginning but the $fillable in Parents model holds all the columns, that why this confused me.

AlexElementarteilchen

4 years ago

Level 9

@cianekaj Ok - then it's not that :)

Can you try to look into the DB table directly? What do you get when you do SELECT * FROM parents WHERE email = '[email protected]'?

cianekaj

4 years ago

Level 1

@AlexElementarteilchen Yes I get the record where email ='[email protected]'

Snapey

4 years ago

Level 122

@cianekaj no, WHAT do you get? WHAT are the attributes

cianekaj

4 years ago

Level 1

@Snapey below is the attribute and result: attributes:

(id,	full_name,	street_address,	city,	county,	state,	zip,	phone_number,	email,	client_status,	client_type,	assistance,	details,	latitude,	longitude,	created_by,	created_at,	updated_at)
values(2,	Grace Walters,	764 56th Street Northwest,	Albuquerque,	Bernalillo County,	NM,	87105,	(209)-675-6868,	[email protected],	0,	TANF Participant,	0,		35.0969842,	-106.7026177,	1,	9/27/2021 1:22:40 PM,	9/27/2021 1:22:40 PM)