英文:
how to return multi requests from mysql package in node js
问题
我正在开发一个项目,其中包括一个简单的仪表板。在我的服务器上,我建立了三个 MySQL 连接,并获取一些信息,例如 (用户、管理员、事件),这些信息将显示在仪表板上。我使用以下代码来收集信息:
function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
sql.query("SELECT * FROM users", (err, users) => { info.users = users });
sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => { info.admins = admins });
sql.query("SELECT * FROM events", (err, events) => { info.events = events });
callback(info);
}
然后,在我的 GET 请求中使用上述函数:
app.get("/", loginRequired, (req, res, next) => {
// 检查用户是否是管理员 - 以渲染仪表板
if (req.user.isAdmin) {
gather_info((info) => {
return res.render('admin', { events: info.events, users: info.users, admins: users.admins })
})
};
// 如果用户不是管理员,则渲染用户页面
sql.query("SELECT * FROM events WHERE userId = ?", [req.user.ID], (err, events) => {
res.render("index", { events });
});
})
-
现在的问题是,这个 函数不正常工作,我尝试使用 async/await,但 MySQL 不支持这样的用法,我现在该怎么办?
-
还有一个问题,我是否应该在每次打开连接时关闭连接,而我正在使用 createPool?
- 感谢您的善意 -
英文:
i'm working on a project which has a simple dashboard, on my server i make three mySql connections and get some information like ( users, admins ,events ) which will be shown on the dashboard, i use this code to gather information :
function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
sql.query("SELECT * FROM users", (err, users) => { info.users = users });
sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => { info.admins = admins });
sql.query("SELECT * FROM events", (err, events) => { info.events = events });
callback(info);
}
then i use above function in my get request :
app.get("/", loginRequired, (req, res, next) => {
//check if user is admin - to render dashboard
if (req.user.isAdmin) {
gather_info((info) => {
return res.render('admin', { events: info.events, users: info.users, admins: users.admins })
})
};
//if user not admin render users page
sql.query("select * from events where userId = ?", [req.user.ID], (err, events) => {
res.render("index", { events });
});
})
-
now the problem is that the function doesn't work properly, i tried to use async/await but mysql doesn't work like that, what should i do now ?
-
and another question, should i close connection every time i open one while i'm using createPool ?
- thanks for your kindness -
答案1
得分: 2
你可以使用 Promise.all
来优化 gather_info
方法。
function mysqlSyncQuery(q) {
return new Promise((resolve, reject) => {
sql.query(q, (err, result) => {
if(err) return reject(err);
return resolve(result);
});
})
}
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
let q1 = mysqlSyncQuery("SELECT * FROM users");
let q2 = mysqlSyncQuery("SELECT * FROM users WHERE isAdmin = 1");
let q3 = mysqlSyncQuery("SELECT * FROM events");
let results = await Promise.all([q1, q2, q3]);
info.users = results[0];
info.admins = results[1];
info.events = results[2];
callback(info);
}
你还可以在 Promise.all
的 catch 块中添加验证,并解析和验证结果数组。
参考 Promise.all。
Promise.all
会并行运行这些查询,因此您无需等待一个查询完成后再开始另一个查询。这样做会更快,因为这些查询似乎是独立的。
另外,您还可以使用 async await
重构 app.get
,以使代码保持一致。我没有触及那部分,因为您可以轻松处理它。
英文:
You can use Promise.all
to optimise the gather_info
method.
function mysqlSyncQuery(q) {
return new Promise((resolve, reject) => {
sql.query(q, (err, result) => {
if(err) return reject(err);
return resolve(result);
});
})
}
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
let q1 = mysqlSyncQuery("SELECT * FROM users");
let q2 = mysqlSyncQuery("SELECT * FROM users WHERE isAdmin = 1");
let q3 = mysqlSyncQuery("SELECT * FROM events");
let results = await Promise.all([q1, q2, q3]);
info.users = results[0];
info.admins = results[1];
info.events = results[2];
callback(info);
}
You can further add validations in the catch block of Promise.all
and also parse and validate the results array.
For reference Promise.all
Promise.all
will run these queries in parallel so that you don’t have to wait for one to finish before starting another query. This way, it will be faster since these queries seems independent.
More to this, you can also refactor the app.get
with async await
to make the code similar throughout. I haven’t touched that since you can figure it out easily.
答案2
得分: 1
有至少3种方法可以做到这一点。
- 嵌套调用,最可能是最差的方法!
function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
sql.query("SELECT * FROM users", (err, users) => {
info.users = users;
sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => {
info.admins = admins;
sql.query("SELECT * FROM events", (err, events) => {
info.events = events;
callback(info);
});
});
});
}
- async/await,更好但查询是依次调用的,而不是并行调用,这意味着你必须等待每个查询完成才能调用另一个查询。
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
info.users = await query('SELECT * FROM users');
info.admins = await query('SELECT * FROM users WHERE isAdmin = 1');
info.events = await query('SELECT * FROM events');
callback(info);
}
function query(q) {
return new Promise(function(resolve, reject){
sql.query(q, (err, data) => {
resolve(data);
});
});
};
- Promise.all,这应该并行运行所有查询。
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
let users = query('SELECT * FROM users');
let admins = query('SELECT * FROM users WHERE isAdmin = 1');
let events = query('SELECT * FROM events');
Promise.all([users, admins, events]).then((values) => {
info.users = values[0];
info.admins = values[1];
info.events = values[2];
callback(info);
});
}
function query(q) {
return new Promise(function(resolve, reject){
sql.query(q, (err, data) => {
resolve(data);
});
});
};
英文:
There are at least 3 ways to do this.
- nested calls, most likely the worst!!
function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
sql.query("SELECT * FROM users", (err, users) => {
info.users = users;
sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => {
info.admins = admins;
sql.query("SELECT * FROM events", (err, events) => {
info.events = events;
callback(info);
});
});
});
}
- async/await, better but the queries are called one after another instead of in parallel, meaning you are waiting for each query to finish before you call another
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
info.users = await query('SELECT * FROM users');
info.admins = await query('SELECT * FROM users WHERE isAdmin = 1');
info.events = await query('SELECT * FROM events');
callback(info);
}
function query(q) {
return new Promise(function(resolve, reject){
sql.query(q, (err, data) => {
resolve(data);
});
});
};
- Promise.all, this should run all queries in parallel
async function gather_info(callback) {
let info = {
users: [],
admins: [],
events: []
}
let users = query('SELECT * FROM users');
let admins = query('SELECT * FROM users WHERE isAdmin = 1');
let events = query('SELECT * FROM events');
Promise.all([users, admins, events]).then((values) => {
info.users = values[0];
info.admins = values[1];
info.events = values[2];
callback(info);
});
}
function query(q) {
return new Promise(function(resolve, reject){
sql.query(q, (err, data) => {
resolve(data);
});
});
};
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论