How to implement nested query in sqlite3

Question

So i have this 2-layer query in node.js, each query could return multiple results. My code actually just ignores that for now. This is the best i can get, it seems working.

How to correct it please, i don't know how to callback for the 2nd one.

Also the db.close() is always called before the 2nd query finishes, even i have serialize().

var getInfo1Db = function(callback) { var db = new sqlite3.Database("DB.sqlite3");

var cnt = 0; 
var info1JsonObj = [];

db.all("select * from Info1DB", 
    function(err, rows) {

        db.serialize(function() {
            for(var ii=0, len=rows.length; ii<len; ii++) {

                var t2 = rows[ii].info1;
                var doorId =  ...

                db.all("select * from DoorDB where ObjectID=" + doorId,
                    function(err, row2) {

                        if(err) {
                        } else {
                            var doorName = row2[0]...

                            var info1JsonElem = {
                                "DoorName" : doorName
                            };

                            info1JsonObj.push(info1JsonElem);
                            cnt++;
                            if(cnt === rows.length) {
                                callback(null, info1JsonObj);
                            }
                         }
                     }
                 ); // for the only door info based on door id

            } // for each row of info1

            db.close(); // why this finishes before the 2nd db.all
        } );  // end of serialize

});

};

why don't you write a single query instead of two (more like another query per each tuple in the first query)? — dmg
– dmg, Commented Nov 9, 2016 at 22:03
@dmg i simplified the real problem, each query could return multiple results, then your 2nd query uses previous result, then your 3rd query uses ... In the end i just do all these in perl, let node.js calls perl. — user3552178
– user3552178, Commented Nov 9, 2016 at 23:01

Siwei · Accepted Answer · 2021-06-08 14:13:38Z

You can't implement nested query in sqlite3's normal way. ( I mean you even can't do it in the callback hell way, because the sqlite3 need to close the connection before another query called. otherwise you will always got error)

You have to use Promise, async and await to do this. ( it's worth to spend 30 minutes to learn these 3 words )

Step1. define a async function like this:

async query_1() {
  new Promise(resolve => {

      db = ...
      db.serialize( () => {
        db.get('select .. from ... where id = 1', [], (error, row) => {
           // here is the KEY: put the result into resolve 
           // this equals to the "return" statement in non-sync method.
           resolve(row)
        }
      })
      db.close()
  })
}

and also implement your query_2 function like this:

async query_2() {
  let query_1_result = await this.query_1()

  db = ...
  db.serialize( () => {
    db.get('select .. from ... where dependency_id = ' + query_1_result, [], (error, row) => {
       // other code here...
    }
  })
  db.close()
}

refer to my answer: https://stackoverflow.com/a/67881159/445908

Kevin Cyu · Accepted Answer · 2016-12-01 05:35:30Z

How about using 2 function to do these ?

function db_query1(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

function db_query2(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

And call these function:

db_query1(....,function(err,result1){
   if(err) ...// return 
   // do the things with result1
   // And then call query2
   db_query2(....,function(err,result2){
      if(err) ...// return 
      // do the things with result1
   });
});

Hope this will help :)

tejkweku · Accepted Answer · 2020-02-06 16:33:17Z

You can use Promises.all, an array and the second callback for node sqlite3 db.each() that is executed when all rows have been fetched. Node Sqlite3 db.each usage to simplify the nested query and

I cannot really get the meaning of the variables you are using thus I assume that each row in Info1DB has a one-to-many relationship with DoorDB on the field doorId.

async function getInfo (callback) {
    sql = "select * from Info1DB;";
    numVersions = 0;
    countVersions = 0;
    info1JsonObj = [];
    db.serialize(function() {
        db.each(sql, [], (err, info1Row) => {
            sql = "select * from DoorDB where ObjectID=?;";
                info1Row.doors = [];
            doorId = ...
            db.each(sql, [doorId], (err, doorRow) => {
                info1Row.doors.push(new Promise((resolve, reject) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(doorRow);
                    }
                }));
            }, (err, num) => {
                Promise.all(info1Row.doors)
                    .then((doors) => {
                        info1Row.doors = doors;
                        info1JsonObj.push(info1Row);
                        countVersions++;
                        if (countVersions == numVersions) {
                            callback(null, info1JsonObj);
                        }
                    }).catch((err) => {
                        callback(err, null);
                    });
            });
        }, (err, versions) => {
            numVersions = versions;
        });
    });
}

Collectives™ on Stack Overflow

How to implement nested query in sqlite3

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related