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

如何用Laravel Query Builder实现指定Eloquent查询并保持输出结构？

avatar
云原生阿岚
2026-7-24
如何用Laravel Query Builder实现与Eloquent一致的嵌套输出结构

原Eloquent查询代码 

$model->select(
        'product_templates.id',
        'product_templates.name',
        'product_templates.short_name',
        'product_templates.code',
    )
    ->withWhereHas('products', function ($query)  {
        $query->select(
            'products.id',
            'products.product_template_id',
            'products.category_id',
            'products.sub_category_id',
            'products.company_id',
            'products.product_size_id',
            'products.expire_date',
        )
            ->when(request('sub_category_id'), function ($query)  {
                $query->where('sub_category_id', request('sub_category_id'));
            })
            ->where('company_id', request('company_id'))
            ->where('category_id', request('category'))
            ->with('productSize:id,name')
            ->withSum('stocks', 'quantity')
            ->withSum('orderItems', 'quantity');
    })
    ->orderBy('name')
    ->get();

预期的嵌套输出结构 

[{
    "id": 1,
    "name": "ABIES CANADENSIS-PINUS CANADENSIS",
    "short_name": "ABIES-C",
    "code": "1",
    "pt_image": "uploads/product-templates/1667768874.jpg",
    "products": [
    {
        "id": 1,
        "product_template_id": 1,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 6,
        "expire_date": "2024-06-28T00:00:00.000000Z",
        "stocks_sum_quantity": "346",
        "order_items_sum_qty": null,
        "price": {},
        "product_power": null,
        "product_size": {
            "id": 6,
            "name": "30 ML"
        }
    },
    {
        "id": 2,
        "product_template_id": 1,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 7,
        "expire_date": "2023-05-03T00:00:00.000000Z",
        "stocks_sum_quantity": "194",
        "order_items_sum_qty": null,
        "price": {},
        "product_power": null,
        "product_size": {
            "id": 7,
            "name": "100 ML"
            }
    },
    {
        "id": 3,
        "product_template_id": 1,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 15,
        "expire_date": "2024-06-18T00:00:00.000000Z",
        "stocks_sum_quantity": "237",
        "order_items_sum_qty": null,
        "price": {
        "id": 3,
        "product_id": 3,
        "shop_id": 1,
        "mrp": 110,
        "selling_price": 115,
        "purchase_price": 100,
        "created_at": "2023-04-21T13:55:14.000000Z",
        "updated_at": "2023-04-21T13:55:14.000000Z"
        },
        "product_power": null,
        "product_size": {
            "id": 15,
            "name": "1000 ML"
         }
    }]
},

{
    "id": 2,
    "name": "ABIES NIGRA",
    "short_name": "ABIES-N",
    "code": "2",
    "pt_image": "uploads/product-templates/1547250735.jpg",
    "products": [
    {
        "id": 51,
        "product_template_id": 2,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 6,
        "expire_date": "2023-11-12T00:00:00.000000Z",
        "stocks_sum_quantity": "324",
        "order_items_sum_qty": null,
        "price": {
        "id": 51,
        "product_id": 51,
        "shop_id": 1,
        "mrp": 110,
        "selling_price": 115,
        "purchase_price": 100,
        "created_at": "2023-04-21T13:55:16.000000Z",
        "updated_at": "2023-04-21T13:55:16.000000Z"
        },
        "product_power": null,
        "product_size": {
            "id": 6,
            "name": "30 ML"
        }
    },
    {
        "id": 52,
        "product_template_id": 2,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 7,
        "expire_date": "2024-10-01T00:00:00.000000Z",
        "stocks_sum_quantity": "203",
        "order_items_sum_qty": null,
        "price": {
        "id": 52,
        "product_id": 52,
        "shop_id": 1,
        "mrp": 110,
        "selling_price": 115,
        "purchase_price": 100,
        "created_at": "2023-04-21T13:55:16.000000Z",
        "updated_at": "2023-04-21T13:55:16.000000Z"
        },
        "product_power": null,
        "product_size": {
            "id": 7,
            "name": "100 ML"
        }
    },
    {
        "id": 53,
        "product_template_id": 2,
        "category_id": 1,
        "sub_category_id": 1,
        "company_id": 1,
        "product_power_id": null,
        "product_size_id": 15,
        "expire_date": "2024-08-07T00:00:00.000000Z",
        "stocks_sum_quantity": "143",
        "order_items_sum_qty": null,
        "price": {
        "id": 53,
        "product_id": 53,
        "shop_id": 1,
        "mrp": 110,
        "selling_price": 115,
        "purchase_price": 100,
        "created_at": "2023-04-21T13:55:16.000000Z",
        "updated_at": "2023-04-21T13:55:16.000000Z"
        },
        "product_power": null,
        "product_size": {
            "id": 15,
            "name": "1000 ML"
        }
    }]
}]

尝试的Query Builder代码（返回扁平集合） 

return Product::query()
    ->select(
        'products.*',
    )
    ->where('products.category_id', request()->category)
    ->when($subCategoryId = request('sub_category_id'), function ($query) use ($subCategoryId) {
        $query->where('products.sub_category_id',  $subCategoryId);
    })
    ->when($companyId = request('company_id'), function ($query) use ($companyId) {
        $query->where('products.company_id',  $companyId);
    })

    ->join('sub_categories',  'sub_categories.id',  '=', 'products.sub_category_id')
    ->join('product_sizes',  'product_sizes.id',  '=', 'products.product_size_id')
    ->join('product_templates', function($join){
        $join->on('product_templates.id',  '=', 'products.product_template_id');
        $join->on('product_template_images.product_template_id',  '=', 'product_templates.id');
    })
    ->orderBy('product_templates.name')
    ->get();

修改后的Query Builder实现方案

Query Builder本身不会自动构建Eloquent那样的关联嵌套结构，需要手动分步骤查询并组装数据，以下是实现代码：

use Illuminate\Support\Collection;
use stdClass;

// 1. 查询符合条件的产品模板（带图片字段）
$productTemplates = DB::table('product_templates')
    ->select(
        'product_templates.id',
        'product_templates.name',
        'product_templates.short_name',
        'product_templates.code',
        'product_template_images.path as pt_image'
    )
    ->join('product_template_images', 'product_template_images.product_template_id', '=', 'product_templates.id')
    // 筛选存在符合条件产品的模板，对应原Eloquent的withWhereHas
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('products')
            ->whereColumn('products.product_template_id', 'product_templates.id')
            ->where('products.category_id', request('category'))
            ->when(request('sub_category_id'), function ($q) {
                $q->where('products.sub_category_id', request('sub_category_id'));
            })
            ->where('products.company_id', request('company_id'));
    })
    ->orderBy('product_templates.name')
    ->get();

// 2. 查询符合条件的产品，包含库存和订单项的求和字段
$products = DB::table('products')
    ->select(
        'products.id',
        'products.product_template_id',
        'products.category_id',
        'products.sub_category_id',
        'products.company_id',
        'products.product_size_id',
        'products.expire_date',
        DB::raw('COALESCE(SUM(stocks.quantity), 0) as stocks_sum_quantity'),
        DB::raw('COALESCE(SUM(order_items.quantity), 0) as order_items_sum_qty')
    )
    ->leftJoin('stocks', 'stocks.product_id', '=', 'products.id')
    ->leftJoin('order_items', 'order_items.product_id', '=', 'products.id')
    ->where('products.category_id', request('category'))
    ->when(request('sub_category_id'), function ($q) {
        $q->where('products.sub_category_id', request('sub_category_id'));
    })
    ->where('products.company_id', request('company_id'))
    ->groupBy('products.id')
    ->get();

// 3. 批量查询关联的产品规格和价格，避免N+1查询
$productSizes = DB::table('product_sizes')
    ->select('id', 'name')
    ->whereIn('id', $products->pluck('product_size_id')->unique())
    ->get()
    ->keyBy('id');

$prices = DB::table('prices')
    ->select('*')
    ->whereIn('product_id', $products->pluck('id')->unique())
    ->get()
    ->keyBy('product_id');

// 4. 组装嵌套结构
$result = $productTemplates->map(function ($template) use ($products, $productSizes, $prices) {
    // 筛选当前模板下的所有产品
    $templateProducts = $products->where('product_template_id', $template->id)->map(function ($product) use ($productSizes, $prices) {
        return [
            'id' => $product->id,
            'product_template_id' => $product->product_template_id,
            'category_id' => $product->category_id,
            'sub_category_id' => $product->sub_category_id,
            'company_id' => $product->company_id,
            'product_power_id' => null, // 原输出中为null，根据实际表结构调整
            'product_size_id' => $product->product_size_id,
            'expire_date' => $product->expire_date,
            'stocks_sum_quantity' => (string)$product->stocks_sum_quantity,
            'order_items_sum_qty' => $product->order_items_sum_qty ? (string)$product->order_items_sum_qty : null,
            'price' => $prices->get($product->id, new stdClass()), // 无价格则返回空对象
            'product_power' => null,
            'product_size' => $productSizes->get($product->product_size_id, ['id' => null, 'name' => null])
        ];
    });

    return [
        'id' => $template->id,
        'name' => $template->name,
        'short_name' => $template->short_name,
        'code' => $template->code,
        'pt_image' => $template->pt_image,
        'products' => $templateProducts->values()->toArray()
    ];
});

return $result;

关键说明

  • 用whereExists筛选存在符合条件产品的模板，对应原Eloquent的withWhereHas逻辑
  • 通过leftJoin和groupBy实现withSum的求和效果
  • 批量查询关联数据（产品规格、价格），避免多次数据库查询
  • 利用Collection的map和where方法手动组装嵌套结构，确保与原Eloquent输出一致

内容的提问来源于stack exchange，提问作者Shumon Pal

相关产品推荐
方舟 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.07.24 03:52:01