Count records of statuses(approved, pending, rejected) and return values into their own individual variables
I am trying to count database records in mongoDB (using mongoose) where records have a status of pending and approved as well as rejected. So, I am basically trying to get a result where I can show a count of each and display it in my view ie:
Pending: 35
Approved: 97
Rejected: 12
And I want to hold these in variables that I can use in different places ie:
const pending = ....
const approved = ....
const rejected = .....
I have tried to use the aggregate function and run a loop which gets me all the data I need but I don't know how to actually get the data into the three variables like above.
The above code gives me back this (I have no rejected values yet)
I then ran a loop and restructured the data:
That gives me:
pending: 15
approved: 27
But it still isn't what I actually want. I need to get those values into their own variables. How can I do that?
Pending: 35
Approved: 97
Rejected: 12
And I want to hold these in variables that I can use in different places ie:
const pending = ....
const approved = ....
const rejected = .....
I have tried to use the aggregate function and run a loop which gets me all the data I need but I don't know how to actually get the data into the three variables like above.
Product.aggregate([
{ $group: { _id: { status: "$status" }, totalStatus: { $sum: 1 } } }
])The above code gives me back this (I have no rejected values yet)
[
{
_id: {
status: 'pending'
},
totalStatus: 15
},
{
_id: {
status: 'approved'
},
totalStatus: 27
}
]I then ran a loop and restructured the data:
.then(status => {
for (const current of status) {
const [status, total] = [current._id.status, current.totalStatus];
console.log(status + ': ' + total);
}
next();
})That gives me:
pending: 15
approved: 27
But it still isn't what I actually want. I need to get those values into their own variables. How can I do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I know that is not possible if you want a specific structure you need to adjust it manually after receiving the mongoose's result.
You should be able to get the result directly from the db. Something like:
See docs here
const pending = Product.aggregate( [
{ $match: { status: "Pending" } },
{ $group: { _id: "_id", total: { $sum: 1} } }
] );See docs here
ASKER
Thanks Michael, that would only return the pending status and not approved and rejected though?
Yes you're matching on the status field = "Pending". All three constants would look like:
const pending = Product.aggregate( [
{ $match: { status: "Pending" } },
{ $group: { _id: "_id", total: { $sum: 1} } }
] );
const approved = Product.aggregate( [
{ $match: { status: "Approved" } },
{ $group: { _id: "_id", total: { $sum: 1} } }
] );
const rejected = Product.aggregate( [
{ $match: { status: "Rejected" } },
{ $group: { _id: "_id", total: { $sum: 1} } }
] );ASKER
Got it. But that is 3 different queries now. I had wanted to do it all in one query and try extract that way so I only hit the database once as opposed to 3 times. Or is there not much difference in performance?
Got it, I misunderstood. So your question is really how to get the values from your results array:
Zakaria Acharki provided an answer on that. You could also use .filter, something like:
Of course you're going to have to handle instances were the status is not present, like rejected. Something like:
[ {
_id: {
status: 'pending'
},
totalStatus: 15
},
{
_id: {
status: 'approved'
},
totalStatus: 27
} ]Zakaria Acharki provided an answer on that. You could also use .filter, something like:
var result = [ {
_id: {
status: 'pending'
},
totalStatus: 15
},
{
_id: {
status: 'approved'
},
totalStatus: 27
} ];
const pending = result.filter(item => item._id.status === "pending")[0].totalStatus;Of course you're going to have to handle instances were the status is not present, like rejected. Something like:
var rejected = 0;
if (result.filter(item => item._id.status === "rejected")) {
rejected = result.filter(item => item._id.status === "rejected")[0].totalStatus;
}
ASKER