高效从MySQL多表查询并构建嵌套JSON数据结构教程

// 1. 查询所有产品
$products = DB::query("SELECT id, product_name, price FROM products");

// 2. 循环每个产品，单独查询其图片（N次查询）
foreach ($products as &$product) {
    $product['images'] = DB::query("SELECT id, image FROM product_images WHERE product_id = ?", [$product['id']]);
}

// 最终 $products 结构符合要求，但性能低下

<?php

/**
 * 模拟从数据库获取的产品数据
 */
$products_data = [
    ['id' => 1, 'product_name' => 'product name 1', 'price' => 15],
    ['id' => 2, 'product_name' => 'product name 2', 'price' => 23.25],
    ['id' => 3, 'product_name' => 'product name 3', 'price' => 50],
];

// 提取所有产品ID，用于构建IN子句
$product_ids = array_column($products_data, 'id'); // 结果: [1, 2, 3]

/**
 * 模拟从数据库获取的图片数据
 * 实际中会使用 SQL 查询：SELECT id, product_id, image FROM product_images WHERE product_id IN (1, 2, 3)
 */
$images_data = [
    ['id' => 1, 'product_id' => 1, 'image' => 'e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png'],
    ['id' => 2, 'product_id' => 1, 'image' => 'sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png'],
    ['id' => 3, 'product_id' => 1, 'image' => '7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png'],
    // 假设产品2和3没有图片，或者这里只获取了产品1的图片
    // 实际场景中，WHERE product_id IN (...) 会返回所有匹配的图片
];

// 初始化一个用于按产品ID分组图片的数组
$images_by_product_id = [];
foreach ($images_data as $image) {
    $product_id = $image['product_id'];
    // 移除 product_id 字段，因为前端不需要在每个图片对象中重复此信息
    unset($image['product_id']);
    $images_by_product_id[$product_id][] = $image;
}

// 遍历产品数据，将图片关联到对应的产品
$result = [];
foreach ($products_data as $product) {
    $product_id = $product['id'];
    // 如果有图片，则添加，否则为空数组
    $product['images'] = $images_by_product_id[$product_id] ?? [];
    $result[] = $product;
}

// 输出最终结构
echo json_encode($result, JSON_PRETTY_PRINT);

/* 预期输出示例：
[
    {
        "id": 1,
        "product_name": "product name 1",
        "price": 15,
        "images": [
            {
                "id": 1,
                "image": "e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png"
            },
            {
                "id": 2,
                "image": "sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png"
            },
            {
                "id": 3,
                "image": "7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png"
            }
        ]
    },
    {
        "id": 2,
        "product_name": "product name 2",
        "price": 23.25,
        "images": [] // 如果产品2没有图片
    },
    {
        "id": 3,
        "product_name": "product name 3",
        "price": 50,
        "images": [] // 如果产品3没有图片
    }
]
*/

SELECT
    p.id,
    p.product_name,
    p.price,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', pi.id,
            'image', pi.image
        )
    ) AS images
FROM
    products AS p
JOIN
    product_images AS pi ON p.id = pi.product_id
GROUP BY
    p.id, p.product_name, p.price;

SELECT
    p.id,
    p.product_name,
    p.price,
    CASE
        WHEN COUNT(pi.id) > 0 THEN JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', pi.id,
                'image', pi.image
            )
        )
        ELSE JSON_ARRAY() -- 如果没有图片，返回空JSON数组
    END AS images
FROM
    products AS p
LEFT JOIN
    product_images AS pi ON p.id = pi.product_id
GROUP BY
    p.id, p.product_name, p.price;

{
  "id": 1,
  "product_name": "product name 1",
  "price": 15,
  "images": [
    {
      "id": 1,
      "image": "e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png"
    },
    {
      "id": 2,
      "image": "sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png"
    },
    {
      "id": 3,
      "image": "7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png"
    }
  ]
}