I really want to make primary array have the property of foreign key
I have 2 table the tipe_akademik and jam_akademik_a
Then the jam_akademik have foreign key to tipe_akademik
The next I want show as tipe_akademik have list of jam_akademik_a being called list_mapel
I want make the array like :
{
"data": [
{
"id": 1,
"tipe_akademik": "a",
"listmapel": [{//from jam_akademik_a//
"id": 1,
"mapel": "Pendidikan Agama Islam",
"tipe_aka": 1
},
{
"id": 10,
"mapel": "Bahasa Indo",
"tipe_aka": 1
}
]
}, and etc
In top of my array the first id is primary key from tipe_akademik while tipe _aka is foreign key to that id , then I try to make 1 array from 2 query
My source code
router.get('/test', function (req ,res) {
//query
let tipe= []
connection.query(`SELECT * FROM tipe_akademik `, function (err, result){
tipe=result
let i = 0
for (let mapeltipe of tipe ){connection.query ('SELECT * FROM jam_akademik_a WHERE tipe_aka = ? ' ,[mapeltipe.id] ,function (err, listmapel){
tipe[i].listmapel = listmapel
i++
}
)}return res.status(500).json ({data: tipe})
}
)
});
But when I return it the array tipe still showing the first query only. The array I doing for looping is not saved in the array tipe. Thanks if someone can help me... for making that 2 query in one array with the looping.
There are two things you need to do.
return statement will not work as you expect it to. You will have to await the result. I recommend you google for JavaScript async/ await for more details and explanation. This thread might also help.INNER JOIN in order to combine (it's actually an intersection of the given column values) the two tables. I recommend you also google SQL JOIN if you are unfamiliar.With both those changes applied you would end up with a solution similar to this.
Disclaimer: This is untested and I had to make some assumptions on the database field names in order to create the query but conceptually this should work.
// you can only use async in an async function so make the function async
router.get("/test", async function (req, res) {
// await until the Promise is rejected or resolved
const data = await getDatabaseData();
return res.status(500).json({ data: data });
});
async function getDatabaseData(){
// return a promise immediately and once the callback returns return an error (reject the promise) or the data (resolve the promise)
return new Promise((resolve, reject) => {
// That's a guess but if you have a foreign key you can JOIN, just search for SQL JOIN and you will find lots of examples/ explanations
connection.query(`SELECT * FROM tipe_akademik AS t INNER JOIN jam_akademik_a AS j ON j.listmapel = t.tipe_aka;`, (err, result) => {
// if an error occurred reject promise
if(err) reject(err);
// we have the data so we can resolve the promise
else resolve(result);
})
})
}