I am developing an api with NodeJS and MySQL. What I want to achieve is that when listing the users and relating the tables, they can be seen as objects. That is, the profile table or the history table, be an object within the user data.
I tried to look for this question, but I can't solve it or I must be missing something.
I leave specified how I am working. Below are the results; the current one and the one I'm trying to get.
Users table
id | nombre | email
----------------------------
1 | pepe | [email protected]
2 | luis | [email protected]
3 | ana | [email protected]
Profile table
id_perfil | id_user | direccion | fecha_nac
--------------------------------------------
1 | 1 | calle 1 | 2000-02-12
2 | 2 | calle 2 | 1990-06-26
3 | 3 | calle 3 | 2003-11-03
script
const getUsers = async(req, res) => {
try{
const connection = await getConnection();
const result = await connection.query(`SELECT
u.*,
p.direccion,
p.fecha_nac
FROM
usuario u
INNER JOIN
perfil p
WHERE u.id = p.id_user
`);
// console.log(result);
res.json(result);
} catch(error){
res.status(500);
res.send(error.message);
}
};
Current result
[
{
"id": 1,
"name": "pepe",
"email": "[email protected]",
"direccion": "calle 1",
"fecha_nac": "2000-02-12"
},
{
"id": 2,
"name": "luis",
"email": "[email protected]",
"direccion": "calle 2",
"fecha_nac": "1990-06-26",
},
{
"id": 3,
"name": "ana",
"email": "[email protected]",
"direccion": "calle 3",
"fecha_nac": "2003-11-03"
}
]
Result to obtain
[
{
"id": 1,
"name": "pepe",
"email": "[email protected]",
"perfil": [
{
"id_perfil": 1,
"direccion": "calle 1",
"fecha_nac": "2000-02-12"
}
]
}
]
Thanks in advance for the help!