php - 如何修复 Laravel "Unknown column in field list"

Question

我正在开发 Laravel 5.7 版本。我有 StockModel ，它与其他模型有关系。当 $stockModel->save() 时，我在 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';
        }

我知道它正在考虑将 StockModel 中不存在的 category 字段作为字段，即使我已指定 $fillables

Answer 1

您遇到此问题的原因是您如何将不同的关系与模型“关联”。您不需要将关系属性分配回对象。这实际上是导致您的问题的原因，因为 Eloquent 如何处理它的关系属性。您实际上在这里所做的是将关系分配为模型的属性，以便稍后保存它时尝试将该模型保存为字段。

我还强烈建议不要循环访问模型来单独加载关系，因为这会导致多个查询和低效的代码。相反，请查看 Eager Loading因为这消除了 n+1 问题，即单独加载每个模型的关系。

如果您使用预先加载，那么您可以完全摆脱您的 associateRelationship ，即

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;
}

<小时/>

另外，仅供引用，您不需要将模型的实例传递给关系方法，您可以只使用 ::class 常量，例如:

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

<小时/>

最后，我还建议查看:

Incrementing and decrement a field
Using whereHas (而不是连接)
Retrieving a single model (而不是集合)