Aggregate Query Mongodb for Rating System-CodePudding

I have 2 collections one is user and other is college. I want a rating schema with aggregate query user 1 gives 2 rating to college 1 I made a rating schema like

rate = mongoose.Schema({
rating: number,
userId: 
collegeid:})

college comes on the basis of score user gets. And then user is allowed to rate the colleges, he has been shown.

So how to write the query to fetch the rating. How can I achieve this?

CodePudding user response：

You can use the built in mongoose populate function if you define your Schema correctly.

Please check the link below

https://mongoosejs.com/docs/populate.html

CodePudding user response：

Let's assume the rate collection has data like this:

var r = [
    { rating: 1, userId: "U1", collegeid: "C1" },
    { rating: 1, userId: "U1", collegeid: "C2" },
    { rating: 3, userId: "U1", collegeid: "C3" },
    { rating: 2, userId: "U2", collegeid: "C1" },
    { rating: 6, userId: "U2", collegeid: "C2" },
    { rating: 7, userId: "U3", collegeid: "C1" },
    { rating: 5, userId: "U3", collegeid: "C4" },
    { rating: 3, userId: "U3", collegeid: "C3" }
];

Here are some useful queries to get started. In general, aggregate() is The New find() (since at least v3.2) and although slightly more complicated for the simplest expressions, it is vastly more powerful and it should be your starting point.

// Lookup all ratings available for collegeid C1:                                         
c=db.rate.aggregate([
    {$match: {"collegeid":"C1"}}
]);

// Lookup all ratings by user U3:                                                         
c=db.rate.aggregate([
    {$match: {"userId":"U3"}}
]);

// Lookup all ratings by user U3 for college C1:                                          
c=db.rate.aggregate([
    {$match: {"userId":"U3","collegeid":"C1"}}
]);

// Lookup all ratings by user U3 OR college C1:                                           
c=db.rate.aggregate([
    {$match: {$or: [
        {"userId":"U3"},
        {"collegeid":"C1"}
    ]}}
]);

// Get counts, avg, max & min ratings for all colleges:                                   
c=db.rate.aggregate([
    {$group: {_id:"$collegeid", n:{$sum:1},
          avg:{$avg:"$rating"},
          max:{$max:"$rating"},
          min:{$min:"$rating"}}}
]);

// Who rated colleges the highest?                                                        
c=db.rate.aggregate([
    {$sort: {"rating":-1}},  // sort descending

    // Use $first operator to capture only the first item for each unique collegeid
    // in the sorted material flowing into this stage:
    {$group: {_id:"$collegeid", who:{$first:"$userId"}, rating:{$first:"$rating"} }}
]);