Mongoose/mongodb - get only latest records per id

Question

I have an Inspection model in mongoose:

var InspectionSchema = new Schema({
    business_id: {
        type: String,
        required: true
    },
    score: {
        type: Number,
        min: 0,
        max: 100,
        required: true
    },
    date: {
        type: Number, // in format YYYYMMDD
        required: true
    },
    description: String,
    type: String
});

InspectionSchema.index({business_id: 1, date: 1}, {unique: true});

It is possible for there to be multiple inspections on the same Business (each Business is represented by a unique business_id). However, there is a limit of one inspection per business per day, which is why there is a unique index on business_id + date.

I also created a static method on the Inspection object which, given a list of business_ids, retrieves all of the inspections for the underlying businesses.

InspectionSchema.statics.getAllForBusinessIds = function(ids, callback) {
    this.find({'business_id': {$in: ids}}, callback);
};

This function fetches all of the inspections for the requested businesses. However, I want to also create a function that fetches only the latest inspection per business_id.

InspectionSchema.statics.getLatestForBusinessIds = function(ids, callback) {
    // query to get only the latest inspection per business_id in "ids"?
};

How might I go about implementing this?

Answer 1

You can use the .aggregate() method in order to get all the latest data in one request:

Inspection.aggregate(
    [
        { "$sort": { "buiness_id": 1, "date": -1 } },
        { "$group": {
            "_id": "$business_id",
            "score": { "$first": "$score" },
            "date": { "$first": "$date" },
            "description": { "$first": "$description" },
            "type": { "$first": "$type" }
        }}
    ],
    function(err,result) {

    }
);

Just $sort then $group with the "business_id" as the grouping key. The $first gets the first results from the grouping boundary, where we already sorted by date within each id.

If you just want the date then do this using $max:

Inspection.aggregate(
    [
        { "$group": {
            "_id": "$business_id",
            "date": { "$max": "$date" }
        }}
    ],
    function(err,result) {

    }
);

Also see $match if you want to "pre-filter" the business id values or any other conditions when doing this.