如何在sqlite3中实现嵌套查询 [英] How to implement nested query in sqlite3

问题描述

所以我在 node.js 中有这个 2 层查询，每个查询可以返回多个结果.我的代码实际上只是暂时忽略了这一点.这是我能得到的最好的，它似乎有效.

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.

1. 请问如何更正，我不知道如何回调第二个.

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

而且 db.close() 总是在第二个查询完成之前被调用，即使我有 serialize().

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

var getInfo1Db = 函数(回调){var db = new sqlite3.Database("DB.sqlite3");

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

});

};

推荐答案

你不能在 sqlite3 的正常方式中实现嵌套查询.(我的意思是你甚至不能以回调地狱的方式来做，因为sqlite3需要在调用另一个查询之前关闭连接.否则你总是会出错)

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)

你必须使用 Promise、async 和 await 来做到这一点.(花30分钟学习这3个字是值得的)

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()
  })
}

并像这样实现你的 query_2 函数:

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()
}

参考我的回答:https://stackoverflow.com/a/67881159/445908

这篇关于如何在sqlite3中实现嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！