如何用Laravel Query Builder实现关联属性的嵌套格式查询结果

格式二：属性为关联数组（类似Eloquent关联格式） #

直接通过SQL的JSON聚合函数从数据库层面生成嵌套数组结构，需要MySQL 5.7+或其他支持JSON函数的数据库：

$products = DB::table('products')
    ->leftJoin('product_properties', 'products.id', '=', 'product_properties.product_id')
    ->selectRaw('
        products.*,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                "product_id", product_properties.product_id,
                "key_name", product_properties.key_name,
                "key_value", product_properties.key_value
            )
        ) as property
    ')
    ->groupBy('products.id')
    ->get();

说明： #

• 使用leftJoin保证没有属性的产品也能被查询到
• JSON_OBJECT构造单个属性的JSON对象
• JSON_ARRAYAGG将同一产品的所有属性对象聚合成一个数组
• 如果是PostgreSQL，替换为json_agg(json_build_object(...))

格式一：属性为键值对对象 #

同样用SQL的JSON聚合函数生成键值对嵌套对象：

$products = DB::table('products')
    ->leftJoin('product_properties', 'products.id', '=', 'product_properties.product_id')
    ->selectRaw('
        products.*,
        COALESCE(
            JSON_OBJECTAGG(product_properties.key_name, product_properties.key_value),
            JSON_OBJECT()
        ) as property
    ')
    ->groupBy('products.id')
    ->get();

说明： #

• JSON_OBJECTAGG将同一产品的key_name作为键、key_value作为值，聚合为一个JSON对象
• COALESCE处理无属性的产品，将property设为空对象而非null
• PostgreSQL环境替换为COALESCE(json_object_agg(key_name, key_value), '{}'::json)

数据库不支持JSON函数时的集合处理方案 #

如果你的数据库版本较低，无法使用JSON聚合函数，可以先查询所有数据，再通过Laravel集合处理成目标格式：

生成关联数组格式 #

$products = DB::table('products')
    ->leftJoin('product_properties', 'products.id', '=', 'product_properties.product_id')
    ->get()
    ->groupBy('id')
    ->map(function ($group) {
        // 取产品基础信息
        $product = $group->first()->toArray();
        // 筛选并整理属性数组
        $product['property'] = $group
            ->filter(fn($item) => !is_null($item->product_id))
            ->map(fn($item) => [
                'product_id' => $item->product_id,
                'key_name' => $item->key_name,
                'key_value' => $item->key_value
            ])
            ->values()
            ->toArray();
        
        return $product;
    })
    ->values();

生成键值对对象格式 #

$products = DB::table('products')
    ->leftJoin('product_properties', 'products.id', '=', 'product_properties.product_id')
    ->get()
    ->groupBy('id')
    ->map(function ($group) {
        $product = $group->first()->toArray();
        // 提取属性键值对
        $properties = $group
            ->filter(fn($item) => !is_null($item->key_name))
            ->pluck('key_value', 'key_name')
            ->toArray();
        
        $product['property'] = empty($properties) ? new \stdClass() : $properties;
        return $product;
    })
    ->values();

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