How to make an array from two queries with looping?

Question

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.

Answer 1

There are two things you need to do.

1. Your results will be returned in a callback function therefore your 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.
2. I am assuming your tables are connected through a foreign key, so you should use that in your SQL query. Let the database do the heavy-lifting for you instead of doing it yourself in JavaScript, that's what you have a database for. You can do a so called 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);
    })
  })
}