Count records of statuses(approved, pending, rejected)  and return values into their own individual variables

Black Sulfur
Black Sulfur used Ask the Experts™
on
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.

    Product.aggregate([
        { $group: { _id: { status: "$status" }, totalStatus: { $sum: 1 } } }

    ])

Open in new window


The above code gives me back this (I have no rejected values yet)

[
    {
        _id: {
            status: 'pending'
        },
        totalStatus: 15
    },
    {
        _id: {
            status: 'approved'
        },
        totalStatus: 27
    }
  ]

Open in new window


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

Open in new window


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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Developer
Distinguished Expert 2018
Commented:
You could store them in object literal like :

.then(status => {
    const result = {};
    for (const current of status) {
        result[current._id.status] = current.totalStatus;
    }
    next();
})

Open in new window


Then show them after that based in key (status) like :

console.log('Pending : ' + result.pending);
console.log('Approved : ' + result.approved);
console.log('Rejected : ' + result.rejected);

Open in new window


Else if you want really to put them to variables you could do something like :

.then(status => {
    const pending,approved,rejected;

    for (const current of status) {
        switch(status){
          case 'pending':
             pending = current.totalStatus;
             break;
          case 'approved':
             approved = current.totalStatus;
             break;
          case 'rejected':
             rejected = current.totalStatus;
             break;
        }
    }
    next();
})

Open in new window


Then show them like :

console.log('Pending : ' + pending);
console.log('Approved : ' + approved);
console.log('Rejected : ' + rejected);

Open in new window


I prefer the object option since you don't have to create variable manually and keep them empty if you don't have status in your DB.

You could show the content of the object like :

for(var status in result){
   console.log( status + ' : ' + result[status]);
}

Open in new window

Author

Commented:
Thanks for this answer. Is there a way to do it all in Mongoose though instead of me having to loop and use destructuring?
Zakaria AcharkiAnalyst Developer
Distinguished Expert 2018

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Michael VasilevskySolutions Architect

Commented:
You should be able to get the result directly from the db. Something like:

const pending = Product.aggregate( [
    { $match: { status: "Pending" } },
    { $group: { _id: "_id", total: { $sum: 1} } }
] );

Open in new window


See docs here

Author

Commented:
Thanks Michael, that would only return the pending status and not approved and rejected though?
Michael VasilevskySolutions Architect

Commented:
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} } }
] );

Open in new window

Author

Commented:
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?
Michael VasilevskySolutions Architect

Commented:
Got it, I misunderstood. So your question is really how to get the values from your results array:
[   {
        _id: {
            status: 'pending'
        },
        totalStatus: 15
    },
    {
        _id: {
            status: 'approved'
        },
        totalStatus: 27
    }  ]

Open in new window


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;

Open in new window


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;
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial