产品关联多图片查询：如何合并结果为单产品带图片数组？

我需要从数据库查询所有产品及其关联的全部图片，每个产品可对应多张图片。目前的代码实现后，返回的是每张图片对应一个产品对象，不符合预期。我想要的结构是每个产品对象包含一个存储所有关联图片的productImages数组，后续这个数组可能会新增更多字段。

原实现代码 #

async getAllProductAndImages() {
    const productsDatabase = await client.query(`
    SELECT 
    products.*,
    products_images.id AS imageId,
    products_images.name AS imageName,
    products_images.product_id AS productImgId
    FROM products INNER JOIN products_images 
    ON products.id = products_images.product_id`)

    const products = productsDatabase.rows.map(products => {
      const urlImage = `${process.env.APP_API_URL}/files/${products.imagename}`
      const productImage = new ProductImage(products.imagename, products.id)
      productImage.id = products.imageid
      productImage.url = urlImage

      const product = new Product(
        products.name,
        products.description,
        products.price,
        products.amount
      )
      product.id = products.id
      product.productsImages = productImage
        
      return product
    })

    return products
  }

查询返回的原始数据 #

[
{
    "id": "3f671bc1-5163-44c8-88c9-4430d45f1471",
    "name": "a",
    "description": "a",
    "price": "10",
    "amount": 5,
    "imageid": "78eb77d4-bf5a-44c1-a37a-0a28eb0f85ad",
    "imagename": "21bb52fa-9822-4732-88c4-8c00165185d6-sunrise-illustration-digital-art-uhdpaper.com-hd-4.1963.jpg"
},
{
    "id": "3f671bc1-5163-44c8-88c9-4430d45f1471",
    "name": "a",
    "description": "a",
    "price": "10",
    "amount": 5,
    "imageid": "2157284b-34fd-41a4-ac3e-aa4d3f46b883",
    "imagename": "96afbbc7-c604-4cfd-b634-0f39a4f20601-starry_sky_boat_reflection_125803_1280x720.jpg"
}
]

当前代码返回结果 #

[
{
    "id": "3f671bc1-5163-44c8-88c9-4430d45f1471",
    "name": "a",
    "description": "a",
    "price": "10",
    "amount": 5,
    "productsImages": {
        "id": "78eb77d4-bf5a-44c1-a37a-0a28eb0f85ad",
        "name": "21bb52fa-9822-4732-88c4-8c00165185d6-sunrise-illustration-digital-art-uhdpaper.com-hd-4.1963.jpg",
        "url": "http://localhost:3000/files/21bb52fa-9822-4732-88c4-8c00165185d6-sunrise-illustration-digital-art-uhdpaper.com-hd-4.1963.jpg",
        "product_id": "3f671bc1-5163-44c8-88c9-4430d45f1471"
    }
},
{
    "id": "3f671bc1-5163-44c8-88c9-4430d45f1471",
    "name": "a",
    "description": "a",
    "price": "10",
    "amount": 5,
    "productsImages": {
        "id": "2157284b-34fd-41a4-ac3e-aa4d3f46b883",
        "name": "96afbbc7-c604-4cfd-b634-0f39a4f20601-starry_sky_boat_reflection_125803_1280x720.jpg",
        "url": "http://localhost:3000/files/96afbbc7-c604-4cfd-b634-0f39a4f20601-starry_sky_boat_reflection_125803_1280x720.jpg",
        "product_id": "3f671bc1-5163-44c8-88c9-4430d45f1471"
    }
}
]

预期返回结果 #

[
  {
    "id": "3f671bc1-5163-44c8-88c9-4430d45f1471",
        "name": "a",
        "description": "a",
        "price": "10",
        "amount": 5,
    "productImages": [
      {
        "url": "http://localhost:3000/files/21bb52fa-9822-4732-88c4-8c00165185d6-sunrise-illustration-digital-art-uhdpaper.com-hd-4.1963.jpg"
      },
      {
        "url": "http://localhost:3000/files/96afbbc7-c604-4cfd-b634-0f39a4f20601-starry_sky_boat_reflection_125803_1280x720.jpg"
      }
    ]
  }
]

核心是按产品ID分组聚合图片，避免重复生成产品对象。修改后的代码如下：

async getAllProductAndImages() {
    const productsDatabase = await client.query(`
    SELECT 
    products.*,
    products_images.id AS imageId,
    products_images.name AS imageName,
    products_images.product_id AS productImgId
    FROM products INNER JOIN products_images 
    ON products.id = products_images.product_id`)

    // 用Map按产品ID存储已处理的产品，避免重复创建
    const productMap = new Map()

    for (const row of productsDatabase.rows) {
        const urlImage = `${process.env.APP_API_URL}/files/${row.imagename}`
        // 创建图片对象，保留需要的字段
        const productImage = new ProductImage(row.imagename, row.id)
        productImage.id = row.imageid
        productImage.url = urlImage

        if (productMap.has(row.id)) {
            // 产品已存在，直接追加图片到数组
            productMap.get(row.id).productImages.push(productImage)
        } else {
            // 产品不存在，创建新对象并初始化图片数组
            const product = new Product(
                row.name,
                row.description,
                row.price,
                row.amount
            )
            product.id = row.id
            product.productImages = [productImage]
            productMap.set(row.id, product)
        }
    }

    // 将Map中的产品转为数组返回
    return Array.from(productMap.values())
}

代码说明 #

1. 使用Map以产品ID为键存储产品对象，确保每个产品只被创建一次
2. 遍历原始查询结果时，判断产品是否已存在：
   • 存在则将当前图片添加到该产品的productImages数组
   • 不存在则创建新的产品对象，并把当前图片作为数组的第一个元素
3. 最后把Map的值转为数组，得到预期的结构

如果只需要图片的url字段（如预期结果所示），可以简化图片对象的创建：

const productImage = { url: urlImage }

内容的提问来源于stack exchange，提问作者Kauã Pereira