How to fix Laravel "Unknown column <column_name> in field list"

Question

I'm working on Laravel 5.7 version. I have StockModel which has a relationship with other models. While $stockModel->save() I'm getting error in controller.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category' in 'field list'

MySql Error Message : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category' in 'field list' (SQL: update stock set stock_qty = 99, updated_at = 2019-05-18 06:33:16, category = {"category_id":1,"category_name":"Ladies Handbags.","parent_level":0,"category_sequence":1,"is_deleted":0,"created_at":"2019-03-09 10:07:52","updated_at":"2019-03-09 12:13:06"}, product = {"product_id":1,"category_id":1,"product_name":"PERINITA HB","product_code":"00","product_sequence":1,"is_deleted":0,"created_at":"2019-03-10 15:09:12","updated_at":"2019-03-13 09:24:19"}, productVariant = {"product_variant_id":3,"product_id":1,"variant_name":"Pink","variant_code":"HAFD051094M3","variant_sequence":0,"is_deleted":0,"created_at":"2019-03-13 09:24:01","updated_at":"2019-03-13 09:24:19"}, inward = {"inward_id":2,"inward_no":null,"date_of_purchase":null,"supplier_name":"new supplier","supplier_invoice_no":"as","supplier_invoice_date":null,"invoice_amt":0,"is_deleted":0,"created_at":"2019-04-21 11:59:47","updated_at":"2019-04-23 08:52:32"}, inwardItem = {"inward_item_id":16,"inward_id":2,"category_id":1,"product_id":1,"product_variant_id":3,"barcode":"99","qty":12,"price_per_piece":3,"consumer_discount_percentage":3,"consumer_discount_amt":0,"seller_margin_percentage":0,"seller_margin_amt":0,"is_deleted":0,"created_at":"2019-04-23 08:46:56","updated_at":"2019-04-23 08:52:30"} where stock_id = 16)

StockModel.php

 class StockModel extends Eloquent { protected $table = 'stock'; protected $primaryKey = 'stock_id'; protected $fillable = ['inward_id', 'inward_item_id', 'category_id', 'product_id', 'product_variant_id', 'stock_qty', 'initial_qty', 'is_closed', 'is_deleted']; public function fetchData($data) { $stock_info = StockModel::where($data['condition']); $stock_info = $stock_info->get(); if (!empty($stock_info) && isset($data['associate_relationships']) && $data['associate_relationships'] == TRUE) { if ($data['type'] == 'OBJECT' || $data['type'] == 'OBJECT_FIRST') { $this->associateRelationship($stock_info); } } return $stock_info; } protected function associateRelationship($objects, $options = NULL) { $new_object = $objects; $has_multiple_objects = TRUE; if (!isset($objects[0])) { $new_object=[]; $new_object[0] = $objects; $has_multiple_objects = FALSE; } foreach ($new_object as $key => $value) { //get category data $value->category = $value->category; //get products data $value->product = $value->product; //get products variant data $value->productVariant = $value->productVariant; //get inward data $value->inward = $value->inward; //get inward item data $value->inwardItem = $value->inwardItem; } // dd($new_object); if ($has_multiple_objects == FALSE) { $new_object = $new_object[0]; } return $new_object; } public function inward() { $inwardModel = new InwardModel(); return $this->hasOne($inwardModel, 'inward_id', 'inward_id'); } public function inwardItem() { $inwardItemModel = new InwardItemModel(); return $this->hasOne($inwardItemModel, 'inward_item_id', 'inward_item_id'); } public function category() { $categoryModel = new CategoryModel(); return $this->hasOne($categoryModel, 'category_id', 'category_id'); } public function product() { $productModel = new ProductModel(); return $this->hasOne($productModel, 'product_id', 'product_id'); } public function productVariant() { $productVariantModel = new ProductVariantModel(); return $this->hasOne($productVariantModel, 'product_variant_id', 'product_variant_id'); } }

Controller

 $q = [ 'condition' => [ $inwardItemModel_tbl_name . '.barcode' => $search_query['barcode'], $stockModel_tbl_name . '.is_closed' => 0, $stockModel_tbl_name . '.is_deleted' => 0, ], 'joins' => [ 'InwardItemModel' => [ 'enable' => 1, 'condition' => [ $inwardItemModel_tbl_name . '.is_deleted' => 0 ] ], ], 'type' => 'OBJECT_FIRST', 'associate_relationships' => TRUE, ]; $stock = $stockModel->fetchData($q); if (!empty($stock) && $stock->stock_qty > 0) { $qty = (float) $search_query['selected_qty']; $qty = 1; $stock->stock_qty-=$qty; $stock->save(); #ERROR IS OCCURED HERE. $info['success'] = TRUE; $info['msg'] = 'Deducted successfully'; }

I'm aware that it is considering category field which is not in StockModel as a Field even though I have specified $fillables

In your associateRelationship method are you just trying to load the different relationships on to the model (inside the foreach loop)? — Rwd
– Rwd, Commented May 18, 2019 at 7:08
I couldn't find any documentation on the fetchData() method, what does it do? I'd suggest you investigate there, because I suspect it serializes the category() method, which is initially a relationship method, into an attribute, which then somehow eloquent tries to save as a column table since it's present in your $stock model instance. — D. Petrov
– D. Petrov, Commented May 18, 2019 at 7:13
@RossWilson Yes I'm aware of it but I also specified fillable columns in the model and associateRelationship I'll be using them in next process. fetchData() is user defined function which i have specified to get better filtered results. — Pavan Webbeez
– Pavan Webbeez, Commented May 18, 2019 at 7:13

Rwd · Accepted Answer · 2019-05-18 07:43:51Z

The reason you're getting this issue is because of how you're "associating" the different relationships with the models. You don't need to assign the relationship property back to the object. This is actually what is causing your issue due to how Eloquent handles it relationship properties. What you're actually doing here is assigning the relationship as a property of the model so when you come to save it later is then tries to save that model as a field.

I would also strongly recommend not looping through models to load the relationships individually as the leads to multiple queries and inefficient code. Instead, look at Eager Loading as this gets rid of the n+1 problem i.e. loading relationships for each model individually.

If you use eager loading then you can get rid of your associateRelationship entirely i.e.

public function fetchData($data) { $stock_info = StockModel::where($data['condition']); if (isset($data['associate_relationships']) && $data['associate_relationships']) { if ($data['type'] == 'OBJECT' || $data['type'] == 'OBJECT_FIRST') { $stock_info->with('category', 'product', 'productVariant', 'inward', 'inwardItem'); } } return $stock_info; }

Also, just an FYI, you don't need to pass instance of a model to the relationship methods, you can just use the ::class constant instead e.g.:

public function inward() { return $this->hasOne(InwardModel::class, 'inward_id', 'inward_id'); }

Lastly, I would also suggest looking at:

Incrementing and decrement a field
Using whereHas (instead of joins)
Retrieving a single model (instead of a collection)

Collectives™ on Stack Overflow

How to fix Laravel "Unknown column <column_name> in field list"

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related