从查询构建器返回特定的JSON格式

Question

我使用Query Builder通过以下查询从多个连接表中获取结果：

$products = DB::table('products as p')
              ->select(
                  'p.id',
                  'p.name',
                  'p.reference',
                  'p.price',
                  'i.path',
                  'i.name'
                )
              ->join('products_r_images as pri', 'pri.product_id', '=', 'p.id')
              ->join('images as i', 'i.id', '=', 'prd.image_id')
              ->get();

一个产品可以关联多个图像。

通过上面的查询，我得到了以下结果：

[
    {
        id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: "product_1.jpg"
    },
    {
        id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: "product_2.jpg"
    }
]

正如您所看到的，两行是一个产品的退货，而我希望在一行中有文档数据退货，如下所示：

[
    {
        product_id: 3,
        name: "Test",
        reference: "ref-test",
        price: 123,
        image_path: "product/product-3/",
        image_name: 
        [
            "product_1.jpg", "product_2.jpg"
        ]
    }
]

有没有办法直接用Query Builder来做这件事，或者需要另一种处理方法？

Answer 1

如果您只想使用Query Builder执行此操作，那么您应该能够：

$products = DB::table('products as p')
    ->select(
        'p.id',
        'p.name',
        'p.reference',
        'p.price',
        'i.path',
        'i.name'
    )
    ->join('products_r_images as pri', 'pri.product_id', '=', 'p.id')
    ->join('images as i', 'i.id', '=', 'prd.image_id')
    ->get()
    ->groupBy('id')
    ->map(function ($products) {

        $product = $products->first();
        $product->image_name = $products->pluck('image_name');

        return $product;
    });

希望这能有所帮助！