Mysql 避免多个SQL查询的重构代码

我将在Node.js(带有Express)和MySQL(mysql2包)中创建一个简单的Electron 商务数据库.

要获取单个产品，我需要从3个不同的表中获取数据:"Products"、"product_Images"(带有产品ID的外键)和"Review"(也带有产品ID的外键).

我的3个问题:

  const productQuery = `
    SELECT p.name, p.reference AS ref, p.price, p.brand, p.stock_qty, p.product_subcategory_id AS subcategoryId, p.product_promotion_id AS promotionId
    FROM products p
    WHERE id = ?
  `;
  const imagesQuery = `
    SELECT url
    FROM product_images
    WHERE product_id = ?
  `
  const reviewsQuery = `
  SELECT r.content AS reviewContent, r.rate AS reviewRate, r.created_at AS reviewDate, r.reviews_user_id AS reviewUserId
  FROM reviews r
  WHERE reviews_product_id = ?
  ORDER BY reviewDate DESC
  `;
  const [product] = await sql.query(productQuery, id);
  const [images] = await sql.query(imagesQuery, id);
  const [reviews] = await sql.query(reviewsQuery, id);
  return { product, images, reviews };

结果(我的种子中还没有图像，所以数组为空是正常的):

{
    "product": [
        {
            "name": "FAMILY CARE TRIPLE",
            "ref": "2c85da01",
            "price": "246.21",
            "brand": "Wordpedia",
            "stock_qty": "61",
            "subcategoryId": 21,
            "promotionId": null
        }
    ],
    "images": [],
    "reviews": [
        {
            "reviewContent": "Fusce posuere felis sed lacus. Morbi sem mauris, laoreet ut, rhoncus aliquet, pulvinar sed, nisl. Nunc rhoncus dui vel sem.",
            "reviewRate": 5,
            "reviewDate": "2023-03-04T11:12:13.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Proin eu mi. Nulla ac enim. In tempor, turpis nec euismod scelerisque, quam turpis adipiscing lorem, vitae mattis nibh ligula nec sem.",
            "reviewRate": 4,
            "reviewDate": "2022-08-28T04:39:37.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Praesent blandit. Nam nulla. Integer pede justo, lacinia eget, tincidunt eget, tempus vel, pede.",
            "reviewRate": 4,
            "reviewDate": "2022-08-19T17:05:23.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Praesent id massa id nisl venenatis lacinia. Aenean sit amet justo. Morbi ut odio.",
            "reviewRate": 1,
            "reviewDate": "2022-08-19T16:05:44.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Aenean lectus. Pellentesque eget nunc. Donec quis orci eget orci vehicula condimentum.",
            "reviewRate": 1,
            "reviewDate": "2022-06-18T00:50:21.000Z",
            "reviewUserId": 2
        }
    ]
}

结果是我想要的，但我不认为对数据库进行3次查询是优化的.

起初，我try 了这个查询(只是为了获取产品信息和相关 comments ，而不是图片).

`SELECT p.name, p.reference AS ref, p.price, p.brand, p.stock_qty, p.product_subcategory_id AS subcategoryId, p.product_promotion_id AS promotionId, r.content AS reviewContent, r.rate AS reviewRate, r.created_at AS reviewDate, r.reviews_user_id AS reviewUserId
FROM products p
JOIN reviews r
ON p.id = r.reviews_product_id
WHERE p.id = ?`

结果是:

[
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Proin eu mi. Nulla ac enim. In tempor, turpis nec euismod scelerisque, quam turpis adipiscing lorem, vitae mattis nibh ligula nec sem.",
        "reviewRate": 4,
        "reviewDate": "2022-08-28T04:39:37.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Aenean lectus. Pellentesque eget nunc. Donec quis orci eget orci vehicula condimentum.",
        "reviewRate": 1,
        "reviewDate": "2022-06-18T00:50:21.000Z",
        "reviewUserId": 2
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Fusce posuere felis sed lacus. Morbi sem mauris, laoreet ut, rhoncus aliquet, pulvinar sed, nisl. Nunc rhoncus dui vel sem.",
        "reviewRate": 5,
        "reviewDate": "2023-03-04T11:12:13.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Praesent blandit. Nam nulla. Integer pede justo, lacinia eget, tincidunt eget, tempus vel, pede.",
        "reviewRate": 4,
        "reviewDate": "2022-08-19T17:05:23.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Praesent id massa id nisl venenatis lacinia. Aenean sit amet justo. Morbi ut odio.",
        "reviewRate": 1,
        "reviewDate": "2022-08-19T16:05:44.000Z",
        "reviewUserId": 1
    }
]

我的问题是，即使结果在技术上是正确的(我得到了我要求的所有数据)，使用它也更困难(与我当前的代码相比)，我猜它的效率不是很高，因为它有一些数据多次(每次不同的审查都会重复关于产品的相同数据).

所以我的问题是:有没有一种方法可以得到与我实际的结果一样的结果，但只需一次查询？ 如果不是，我应该继续使用我的实际代码，还是应该使用我的第一个代码？