Laravel Eloquent关联过滤：仅返回fk_customer_id非空的序列化项

系统逻辑：商品售出后，serializations表的fk_customer_id字段会设为非空值；商品处于库存状态时，该字段保持为NULL。退货页面仅允许已售出的商品发起退货，但当前returnInvoice函数中，用户输入serial_emei_number查询时，仍会返回未售出的商品。

问题原因 #

在switch的default分支（处理serial_emei_number查询的逻辑）中，serialization模型的查询没有添加fk_customer_id非空的过滤条件，导致未售出的商品也被纳入结果集。

修改后的代码 #

public function returnInvoice(Request $request, string $type){
    switch ($type) {
        case 'invoice_no':
            return Receiving::with("serializeItems", "serializeItems.fk_receiving_id.fk_vendor_id")
            ->whereNotNull("fk_customer_id")
            ->whereLike(["invoice_no"], $request->q)
            ->WhereHasLike("fk_vendor_id", ["name", "primary_phone"], $request->q)
            ->WhereHasLike("serializeItems", ["serial_emei_number"], $request->q)
            ->leftJoin('vendors', 'receivings.fk_vendor_id', '=', 'vendors.id') 
            ->selectRaw("receivings.*, CONCAT(invoice_no, ' : ', vendors.name, ' : ', vendors.primary_phone) as text")
            ->groupBy("$request->column")->withSum("invoiceItem", 'subtotal')->get()->take(10);
            break;
        
        default:
            return serialization::with(["fk_receiving_id", "fk_receiving_id.fk_vendor_id", "fk_receiving_invoice_no"])
            ->whereNotNull('fk_customer_id') // 新增：过滤仅已售出的商品
            ->whereLike(["serial_emei_number"], $request->q)
            ->leftJoin('receivings', 'serializations.fk_receiving_id', '=', 'receivings.id') 
            ->leftJoin('vendors', 'receivings.fk_vendor_id', '=', 'vendors.id') 
            ->selectRaw("serializations.*, CONCAT(serial_emei_number, ' : ', receivings.invoice_no, ' : ', vendors.name) as text")
            ->groupBy("$request->column")
            ->get()->take(10);

            break;
    }
    
}

说明 #

在default分支的serialization查询链中添加->whereNotNull('fk_customer_id')，即可确保只返回serializations表中fk_customer_id非空的已售出商品，符合退货页面的操作权限要求。

内容的提问来源于stack exchange，提问作者Shadir Amjard