I have a database table in MySQL called "cars" where I store information such as "brand", "model", and "color". My goal is to retrieve the data in the following structure:
[
{
brand: "audi",
cars: [
{
model: "coupe",
color: "red"
},
{
model: "a3",
color: "blue"
}
]
},
{
brand: "renault",
cars: [
{
model: "modus",
color: "white"
},
{
model: "clio",
color: "green"
}
]
},
...
]
To achieve this, my current approach involves executing a MySQL query to group by brand and then iterate through the results to fetch all cars for each brand:
const query = "SELECT brand FROM cars GROUP BY brand"
mysql.query(query, values, (err, result) => {
for (let i = 0; i < result.length; i++) {
const query2 = "SELECT model, color FROM cars WHERE brand = ?"
const values2 = [result[i].brand]
mysql.query(query2, values2, (err2, result2) => {
result[i].cars = result2
callback(result)
})
}
})
Although this code works, I am concerned about the efficiency of iterating through multiple MySQL queries. I have researched alternative solutions but haven't found a definitive answer.
Is there a way to achieve the desired output with a single MySQL query? Should I consider fetching all rows from the "cars" table and then manipulating the data using JavaScript? Or is it acceptable to continue with my current method of iterating through MySQL queries?
Any insights or suggestions would be greatly appreciated. Thank you.