Postgresql - List all products with an image array

Kau227Pereira
November 14, 2022
198 views
0 votes
2 Answers

I wanted to list all the products in the database and all the images, today each product can have several images and this way I did it, it returns 1 product object for each image

I tried to do this, but I didn’t get what I expected as I said above

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
  }

productsDatabase.rows return

[
{
    "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"
}
]

return that I have using the code above

[
{
    "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"
    }
   ]

this is the return I expect and maybe there will be more stuff inside the productImages array there in the future

[
  {
    "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"
      }
    ]
  }
]

Answers

Here is the solution considering productsDatabase.rows as arr:

const arr = [
  {
    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",
  },
];

const newArr = [];
arr.forEach(a => {
    if (!newArr.find(na => na.id === a.id)) {
       const data = arr.filter(i => i.id === a.id).map(j => {
        return {
          url: `${process.env.APP_API_URL ? process.env.APP_API_URL : 'http://localhost:3000/'}files/${j.imagename}`
       }});
       newArr.push({
        id: a.id,
        name: a.name,
        description: a.description,
        amount: a.amount,
        productsImages: data,
       })

    }
});

console.log(newArr);

You can just process the array to build a nested structure, but it might be easier to start with a subquery:

async getAllProductAndImages() {
  const {rows} = await client.query(`
    SELECT products.*, (
      SELECT json_agg(json_build_object(
        'id', products_images.id,
        'name', products_images.name
      ))
      FROM products_images
      WHERE products_images.product_id = products.id;
    ) AS images
    FROM products;
  `);
  const products = rows.map(row => {
    const product = new Product(
      row.name,
      row.description,
      row.price,
      row.amount
    );
    product.id = row.id;
    product.productsImages = row.images.map(img => {
      const productImage = new ProductImage(img.name, product.id);
      productImage.id = img.id;
      productImage.url = `${process.env.APP_API_URL}/files/${img.name}`;
      return productImage;
    });
    return product;
  });
  return products
}

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – List all products with an image array

Answers