Hi i have 3 endpoint (diferents querys but same methodologie) and i want to create a function to reduce the amounts of lines in my code.
example of my endpoint (Working and tested)
router.get("/timeFrame/vocal_word", authenticateToken, async (req, res) => {
const query =
"SELECT max(vocal_word.user_id) AS user_id, \
AVG(vocal_word.score) AS avg_score, \
SUM(vocal_word.score) AS sum_score, \
COUNT(*) AS total_inputs, \
min(vocal_word.`date`) AS oldest_date, \
max(vocal_word.`date`) AS newest_date, \
case \
when TIME(`date`) between '00:00:00' and '05:59:59' then 3 \
when TIME(`date`) between '06:00:00' and '09:29:59' then 8 \
when TIME(`date`) between '09:30:00' and '12:29:59' then 11 \
when TIME(`date`) between '12:30:00' and '15:29:59' then 14 \
when TIME(`date`) between '15:30:00' and '18:29:59' then 17 \
when TIME(`date`) between '18:30:00' and '23:59:59' then 20 \
end as 'time_intervals' \
FROM vocal_word \
WHERE `date` >= CURRENT_TIMESTAMP() - interval ? DAY AND vocal_word.user_id = ? \
GROUP BY time_intervals \
ORDER BY time_intervals ASC";
try {
const day = 1 " Day";
const week = 6 " days → 1 week";
const month = 30 " days → 1 Month";
const year = 365 " days → 1 Year";
const total = 5000 " days → Total";
let intervalFrame = [day, week, month, year, total];
let result = [];
for (let i = 0; i < intervalFrame.length; i ) {
const outputQuery = await poolPromise.query(query, [
intervalFrame[i],
req.user.userId,
]);
outputQuery[0].forEach((item) => {
item["interval_frame"] = intervalFrame[i];
});
result = [...result, ...outputQuery[0]];
}
res.status(200).json(result);
} catch (error) {
res.status(500).json("Please contact back-end service");
}
});
Not working
Now when i want to take out the variables and the loop is not working and response with an status 200 with an empty object
async function getTimeFrames(query, userId) {
const day = 1 " Day";
const week = 6 " days → 1 week";
const month = 30 " days → 1 Month";
const year = 365 " days → 1 Year";
const total = 5000 " days → Total";
try{
let intervalFrame = [day, week, month, year, total];
let result = [];
for (let i = 0; i < intervalFrame.length; i ) {
const outputQuery = await poolPromise.query(query, [intervalFrame[i], userId]);
outputQuery[0].forEach((item) => {
item["interval_frame"] = intervalFrame[i];
});
return (result = [...result, ...outputQuery[0]]);
}
} catch(error) {
console.log(error);
}
}
the console.log(error) shows [Promise{<pending>}]
The endpoint the query
router.get("/timeFrame/test", authenticateToken, async (req, res, next) => {
const query =
"SELECT max(vocal_word.user_id) AS user_id, \
AVG(vocal_word.score) AS avg_score, \
SUM(vocal_word.score) AS sum_score, \
COUNT(*) AS total_inputs, \
min(vocal_word.`date`) AS oldest_date, \
max(vocal_word.`date`) AS newest_date, \
case \
when TIME(`date`) between '00:00:00' and '05:59:59' then 3 \
when TIME(`date`) between '06:00:00' and '09:29:59' then 8 \
when TIME(`date`) between '09:30:00' and '12:29:59' then 11 \
when TIME(`date`) between '12:30:00' and '15:29:59' then 14 \
when TIME(`date`) between '15:30:00' and '18:29:59' then 17 \
when TIME(`date`) between '18:30:00' and '23:59:59' then 20 \
end as 'time_intervals' \
FROM vocal_word \
WHERE `date` >= CURRENT_TIMESTAMP() - interval ? DAY AND vocal_word.user_id = ? \
GROUP BY time_intervals \
ORDER BY time_intervals ASC";
try {
let result = getTimeFrames(query, req.user.userId);
console.log([result]);
res.status(200).json(result);
} catch (error) {
next(error)
}
});
I know the query works no doubts on that. And i founded some questions related to these topic with.then and here i want to do it with try-catch
CodePudding user response：
Ok, after looking at your code, I noticed two things:
- You are trying to return the results inside the for loop in the
getTimeFramesfunction:
async function getTimeFrames(query, userId) {
const day = 1 " Day";
const week = 6 " days → 1 week";
const month = 30 " days → 1 Month";
const year = 365 " days → 1 Year";
const total = 5000 " days → Total";
try{
let intervalFrame = [day, week, month, year, total];
let result = [];
for (let i = 0; i < intervalFrame.length; i ) {
const outputQuery = await poolPromise.query(query, [intervalFrame[i], userId]);
outputQuery[0].forEach((item) => {
item["interval_frame"] = intervalFrame[i];
});
// Here, you have to change this
// return (result = [...result, ...outputQuery[0]]);
result = [...result, ...outputQuery[0]]
}
// And return the value outside of the loop
return result;
} catch(error) {
console.log(error);
}
}
- The function is an asynchronous function, so it will return a promise, you need to handle that:
// Here you already defined your function as async
// so you can use the await keyword to get the promise content
router.get("/timeFrame/test", authenticateToken, async (req, res, next) => {
const query =
"SELECT max(vocal_word.user_id) AS user_id, \
AVG(vocal_word.score) AS avg_score, \
SUM(vocal_word.score) AS sum_score, \
COUNT(*) AS total_inputs, \
min(vocal_word.`date`) AS oldest_date, \
max(vocal_word.`date`) AS newest_date, \
case \
when TIME(`date`) between '00:00:00' and '05:59:59' then 3 \
when TIME(`date`) between '06:00:00' and '09:29:59' then 8 \
when TIME(`date`) between '09:30:00' and '12:29:59' then 11 \
when TIME(`date`) between '12:30:00' and '15:29:59' then 14 \
when TIME(`date`) between '15:30:00' and '18:29:59' then 17 \
when TIME(`date`) between '18:30:00' and '23:59:59' then 20 \
end as 'time_intervals' \
FROM vocal_word \
WHERE `date` >= CURRENT_TIMESTAMP() - interval ? DAY AND vocal_word.user_id = ? \
GROUP BY time_intervals \
ORDER BY time_intervals ASC";
try {
// Here you need to await for the result like this:
let result = await getTimeFrames(query, req.user.userId);
console.log([result]);
res.status(200).json(result);
} catch (error) {
next(error)
}
});