0

I am creating a nodejs api for a messenging app (using express), I am using sqlite3 as a database software.

Edit : I am stuck in this function, how can I make it work?

const sqlite3 = require('sqlite3');


function recentConvos(){
 let result = []
 uid = 58;
 db.all('SELECT DISTINCT U.id, U.name FROM users U, messages M WHERE (M.sender='+uid+' OR M.dest='+uid+')  AND (U.id = M.sender OR U.id = M.dest) AND (U.id != '+uid+') ORDER BY M.id DESC',(err,rows)=>{
  result.push(rows)
 });
 return result;
}


let db = new sqlite3.Database('./database.db', (err)=>{
 if(err){
  console.log('ERROR DATABASE')
 }
 else{console.log('CONNECTED TO DATABASE')}
})

console.log(recentConvos())
KRYPT0N
  • 81
  • 5

1 Answers1

1

A simple for loop should do the trick. And you can use rows[i].some_field in your nested query like so:

app.get('/o', (req, res) => {
   db.all('sql query here', (err, rows) => {
      let result = []
         for (i = 0; i < rows.length; i++) {
            db.all('another query ... WHERE something = ?', [rows[i].some_field], (err, rows2) => {
               result.push(rows2.field)
            })
         }
    })
    res.send(result) 
})
Costas
  • 109
  • 8
  • what is that second argument in db.all? – KRYPT0N Apr 15 '20 at 10:23
  • The second argument is an array of parameters that you have in your query. This will replace the question marks and is used for escaping to avoid sql injection. You can read more about it here : https://www.sqlitetutorial.net/sqlite-nodejs/query/ – Costas Apr 15 '20 at 12:25