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"
}
]
}
]
CodePudding user response:
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);
CodePudding user response:
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
}