ChungusPrime's avatar

updateOrCreate will create but not update

Hi all,

I have this function in a controller: public function UpdateItem(Request $request) { ItemUser::updateOrCreate( ['user_id' => auth()->user()->id, 'item_id' => request('item_id')], ['selling_currency' => request('sellingcurrency'),'selling_price' => request('sellingprice'),'selling_quantity' => request('sellingqty'),'buying_currency' => request('buyingcurrency'),'buying_price' => request('buyingprice'), 'buying_quantity' => request('buyingqty')] ); }

It works perfectly for creating new rows, however when I try to update I get this message:

Illuminate\Database\QueryException thrown with message "SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (SQL: update `item_user` set `selling_price` = 2500, `selling_quantity` = 7, `buying_price` = 1500, `buying_quantity` = 3, `updated_at` = 2018-08-15 15:22:09 where `` = 1 and `` = 1)"

It's trying to update where '' =1 and '' = 1, i assume these are the item_id and user_id, so how do I get them into the query?

Thanks!

p.s. how do I format code blocks properly?

0 likes
8 replies
bobbybouwmann's avatar

Your code looks correct to me. However it seems like the columns are not being passed correctly. The correct code should look like this:

 ItemUser::updateOrCreate([
    'user_id' => auth()->user()->id, 
    'item_id' => request('item_id'),
], [
    'selling_currency' => request('sellingcurrency'),
    'selling_price' => request('sellingprice'),
    'selling_quantity' => request('sellingqty'),
    'buying_currency' =>request('buyingcurrency'),
    'buying_price' => request('buyingprice'), 
    'buying_quantity' => request('buyingqty'),
]);

I think it's going wrong here because either the user_id is empty or the request doesn't have a item_id? Can you check that first?

Documentation: https://laravel.com/docs/5.6/eloquent#other-creation-methods

ChungusPrime's avatar

if I dd the request I get this:

+request: ParameterBag {#64 ▼
#parameters: array:8 [▼
  "_token" => "Y0VwRBo2BE27qVkPdho9In7zjzroiDOGIjbYCThW"
  "item_id" => "1"
  "sellingqty" => "7"
  "sellingprice" => "2500"
  "sellingcurrency" => "Roubles"
  "buyingqty" => "3"
  "buyingprice" => "1500"
  "buyingcurrency" => "Roubles"
]

}

should i also pass the user_id in with the request?

if i echo auth()->user()->id;

it returns 1, so i think they're both there?

ChungusPrime's avatar

Not sure if this is helpful but here is my ItemUser Model:

namespace App;

 use Illuminate\Database\Eloquent\Relations\Pivot;

class ItemUser extends Pivot
{
       protected $table = 'item_user';
       protected $fillable = ['item_id', 'user_id', 'selling_currency', 'selling_price', 'selling_quantity', 'buying_currency', 'buying_price', 'buying_quantity'];

   public function user()
    {
      return $this->hasOne('App\User');
     }

      public function item()
     {
         return $this->hasOne('App\Item');
     }

 }
Snapey's avatar

It may be related to it being a pivot and not a true model?

Why not just treat it as a model?

1 like
ChungusPrime's avatar

Okay I have changed it to a Model instead of a Pivot, I retested and got this:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: update item_user set selling_price = 3000, selling_quantity = 6, updated_at = 2018-08-15 21:00:08 where id is null)

Snapey's avatar

If you don't have an auto incrementing id column then you need to set $incrementing=false and set the primary key

ChungusPrime's avatar

I have set the primary key's as both item_id and user_id, and set public $incrementing = false; in the model, however I still get that error

Please or to participate in this conversation.