MySQL多表关联查询与应用层数据聚合：构建产品及其图片嵌套结构

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

// 伪代码示例
$products = $db->query("SELECT id, product_name, price FROM products")->fetchAll();

foreach ($products as &$product) {
    $product_id = $product['id'];
    $images = $db->query("SELECT id, image FROM product_images WHERE product_id = {$product_id}")->fetchAll();
    $product['images'] = $images;
}

echo json_encode($products);

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

SELECT
    p.id,
    p.product_name,
    p.price,
    GROUP_CONCAT(CONCAT_WS(':', pi.id, pi.image) SEPARATOR ';') AS images_str
FROM
    products p
LEFT JOIN
    product_images pi ON p.id = pi.product_id
GROUP BY
    p.id, p.product_name, p.price;

<?php

// 模拟从数据库获取的产品数据
$products_flat = [
    ['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，用于查询图片
$product_ids = array_column($products_flat, 'id');
$product_ids_str = implode(',', $product_ids); // 示例：'1,2,3'

// 模拟从数据库获取的图片数据
// 实际查询可能类似：SELECT id, product_id, image FROM product_images WHERE product_id IN ({$product_ids_str});
$images_flat = [
    ['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没有图片，或者图片数据中没有它们
];

// 第一步：将产品列表转换为以ID为键的关联数组，并初始化图片数组
$products_indexed = [];
foreach ($products_flat as $product) {
    $product['images'] = []; // 初始化为空数组
    $products_indexed[$product['id']] = $product;
}

// 第二步：遍历图片列表，将其添加到对应的产品中
foreach ($images_flat as $image) {
    $product_id = $image['product_id'];
    if (isset($products_indexed[$product_id])) {
        // 移除图片数据中的 product_id，因为在嵌套结构中不再需要
        unset($image['product_id']);
        $products_indexed[$product_id]['images'][] = $image;
    }
}

// 如果需要将关联数组转换回索引数组（例如，为了前端接收的JSON数组格式）
$final_products = array_values($products_indexed);

// 输出结果（通常会进行 json_encode）
echo '<pre>';
var_export($final_products);
echo '</pre>';

/*
期望输出结构示例：
array (
  0 =>
  array (
    'id' => 1,
    'product_name' => 'product name 1',
    'price' => 15,
    'images' =>
    array (
      0 =>
      array (
        'id' => 1,
        'image' => 'e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png',
      ),
      1 =>
      array (
        'id' => 2,
        'image' => 'sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png',
      ),
      2 =>
      array (
        'id' => 3,
        'image' => '7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png',
      ),
    ),
  ),
  1 =>
  array (
    'id' => 2,
    'product_name' => 'product name 2',
    'price' => 23.25,
    'images' =>
    array (
    ),
  ),
  2 =>
  array (
    'id' => 3,
    'product_name' => 'product name 3',
    'price' => 50,
    'images' =>
    array (
    ),
  ),
)
*/