You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Laravel中如何用非表列子查询在WHERE子句筛选数据

avatar
算力阿峰
2026-8-21
Laravel Eloquent 筛选关联子查询计算字段（sale_qty > 0）的实现方法

问题背景

我是Laravel新手，现有一段Eloquent关联子查询代码可正常执行：

Product_Type::addSelect([
    'p_name' => Product::select('p_name')->whereColumn('products.id','product_types.products_id'),
    'product_types.pt_name',
    'sale_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','sale')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
    'return_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','return')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
])->where('product_types.status',1)->get()

我需要筛选出sale_qty > 0的结果，但将计算sale_qty的子查询放入WHERE子句时出现错误：

Product_Type::addSelect([
    'p_name' => Product::select('p_name')->whereColumn('products.id','product_types.products_id'),
    'product_types.pt_name',
    'sale_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','sale')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
    'return_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','return')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
])->where('product_types.status',1)
->whereRaw(Stock::selectRaw('SUM(stock_value)*-1')
    ->whereColumn('stocks.product_types_id','product_types.id')
    ->whereRaw('stock_value < 0')
    ->where('stock_type','sale')
    ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
    ->whereIn('id', Bill::select('stocks_id')
        ->where('status',1)
        ->whereIn('customers_id', Customer::select('id')
            ->where('customers.sale_mans_id',$reqSalemanId))) > 0)->get()

对应的原生MySQL语句可正常得到预期结果：

SELECT 
(SELECT p_name FROM products WHERE id=product_types.products_id) AS p_name, 
pt_name, 
(SELECT SUM(stock_value)*-1 FROM stocks WHERE product_types_id=product_types.id AND stock_value < 0 AND stock_type='sale' AND stock_date BETWEEN '2022-07-01' AND '2022-08-31' AND id IN (SELECT stocks_id FROM bills WHERE status=1 AND customers_id IN (SELECT id FROM customers WHERE sale_mans_id=1))) AS sale_qty, 
(SELECT SUM(stock_value)*-1 FROM stocks WHERE product_types_id=product_types.id AND stock_value < 0 AND stock_type='return' AND stock_date BETWEEN '2022-07-01' AND '2022-08-31' AND id IN (SELECT stocks_id FROM bills WHERE status=1 AND customers_id IN (SELECT id FROM customers WHERE sale_mans_id=1))) AS return_qty 
FROM product_types 
WHERE 
status =1 AND 
(SELECT SUM(stock_value)*-1 FROM stocks WHERE product_types_id=product_types.id AND stock_value < 0 AND stock_type='sale' AND stock_date BETWEEN '2022-07-01' AND '2022-08-31' AND id IN (SELECT stocks_id FROM bills WHERE status=1 AND customers_id IN (SELECT id FROM customers WHERE sale_mans_id=1))) > 0;

由于WHERE子句不允许直接使用非表列，请问如何在Laravel中实现该筛选需求？

解决方案

方法1：修正whereRaw的使用方式

之前的错误是直接将Eloquent查询对象与> 0拼接，正确做法是将子查询转换为原生SQL字符串，同时绑定参数避免注入：

// 提取重复的子查询，减少冗余代码
$saleQtySubquery = Stock::selectRaw('SUM(stock_value)*-1')
    ->whereColumn('stocks.product_types_id','product_types.id')
    ->whereRaw('stock_value < 0')
    ->where('stock_type','sale')
    ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
    ->whereIn('id', Bill::select('stocks_id')
        ->where('status',1)
        ->whereIn('customers_id', Customer::select('id')
            ->where('customers.sale_mans_id',$reqSalemanId)));

$result = Product_Type::addSelect([
    'p_name' => Product::select('p_name')->whereColumn('products.id','product_types.products_id'),
    'product_types.pt_name',
    'sale_qty' => $saleQtySubquery,
    'return_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','return')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
])->where('product_types.status',1)
->whereRaw("({$saleQtySubquery->toSql()}) > 0", $saleQtySubquery->getBindings())
->get();

方法2：使用having子句筛选

sale_qty是查询返回的计算字段，可通过having子句在结果集生成后筛选：

$result = Product_Type::addSelect([
    'p_name' => Product::select('p_name')->whereColumn('products.id','product_types.products_id'),
    'product_types.pt_name',
    'sale_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','sale')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
    'return_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','return')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
])->where('product_types.status',1)
->having('sale_qty', '>', 0)
->groupBy('product_types.id') // 若开启ONLY_FULL_GROUP_BY模式需添加
->get();

方法3：包装成子查询在外层筛选

将原查询作为临时子查询，在外层直接筛选计算字段：

$subquery = Product_Type::addSelect([
    'p_name' => Product::select('p_name')->whereColumn('products.id','product_types.products_id'),
    'pt_name' => 'product_types.pt_name',
    'sale_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','sale')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
    'return_qty' => Stock::selectRaw('SUM(stock_value)*-1')
        ->whereColumn('stocks.product_types_id','product_types.id')
        ->whereRaw('stock_value < 0')
        ->where('stock_type','return')
        ->whereBetween('stock_date',[$reqFromDate,$reqToDate])
        ->whereIn('id', Bill::select('stocks_id')
            ->where('status',1)
            ->whereIn('customers_id', Customer::select('id')
                ->where('customers.sale_mans_id',$reqSalemanId))),
])->where('product_types.status',1);

$result = DB::table($subquery, 'pt')
    ->where('pt.sale_qty', '>', 0)
    ->select('pt.*')
    ->get();

内容的提问来源于stack exchange，提问作者Yasir Mehmood

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.21 03:24:24