How to get the latest and oldest record in mongoose.js (or just the timespan between them)

Question

Basic problem

I have a bunch of records and I need to get latest (most recent) and the oldest (least recent).

When googling I found this topic where I saw a couple of queries:

// option 1
Tweet.findOne({}, [], { $orderby : { 'created_at' : -1 } }, function(err, post) {
  console.log( post );
});
// option 2
Tweet.find({}, [], {sort:[['arrival',-1]]}, function(err, post) {
  console.log( post );
});

Unfortunatly they both error:

TypeError: Invalid select() argument. Must be a string or object.

The link also has this one:

Tweet.find().sort('_id','descending').limit(15).find(function(err, post) {
  console.log( post );
});

and that one errors:

TypeError: Invalid sort() argument. Must be a string or object.

So how can I get those records?

Timespan

Even more ideally I just want the difference in time (seconds?) between the oldest and the newest record, but I have no clue on how to start making a query like that.

This is the schema:

var Tweet = new Schema({
    body: String
  , fid: { type: String, index: { unique: true } }
  , username: { type: String, index: true }
  , userid: Number
  , created_at: Date
  , source: String
});

I'm pretty sure I have the most recent version of mongoDB and mongoose.

EDIT

This is how I calc the timespan based on the answer provided by JohnnyHK:

var calcDays = function( cb ) {
  var getOldest = function( cb ) {
    Tweet.findOne({}, {}, { sort: { 'created_at' : 1 } }, function(err, post) {
      cb( null, post.created_at.getTime() );
    });
  }
    , getNewest = function( cb ) {
    Tweet.findOne({}, {}, { sort: { 'created_at' : -1 } }, function(err, post) {
      cb( null, post.created_at.getTime() );
    });
  }

  async.parallel({ 
    oldest: getOldest
  , newest: getNewest
  }
    , function( err, results ) {
      var days = ( results.newest - results.oldest ) / 1000 / 60 / 60 / 24;
      // days = Math.round( days );
      cb( null, days );
    }
  );
}

Zondra

Answer 1

Mongoose 3.x is complaining about the [] parameter in your findOne calls as the array format is no longer supported for the parameter that selects the fields to include.

Try this instead to find the newest:

Tweet.findOne({}, {}, { sort: { 'created_at' : -1 } }, function(err, post) {
  console.log( post );
});

Change the -1 to a 1 to find the oldest.

But because you're not using any field selection, it's somewhat cleaner to chain a couple calls together:

Tweet.findOne().sort({created_at: -1}).exec(function(err, post) { ... });

Or even pass a string to sort:

Tweet.findOne().sort('-created_at').exec(function(err, post) { ... });

Senter

thanks! Do you know if it's easy to build a query to calc the timespan between the first and last? Or just query the first, the last and calc the timespan myself inside node? – Zondra

I don't think you can do it in a single query. Query both the min and max in parallel and then calc the timespan once both queries complete. Check out async.parallel for doing that sort of thing cleanly. – Senter

I tried this query, but both 'created_at' : -1 or 'created_at' : 1 returns the oldest record, how comes? my mongoose api version is 3.8.X – Flaminius

this method is a old version one, now 3.8 mongoose use model.findOne().sort({ field: 'asc', _id: -1 }).limit(1) to find the last entry or model.findOne().sort({ field: -_id }).limit(1) – Flaminius

@Flaminius - will you publish this as an answer rather than as a comment please. This is my personally preferred solution and I will upvote your answer if you do create it. – Affirm

Unsupported projection option: sort: { created_at: -1 } when i use this – Roccoroch

@Roccoroch You may have forgotten one of the two {} parameters that must precede that parameter for it to be interpreted as query options. – Senter

sorting on the _id is faster than sorting on created_at (because of indexing) – Respecting

@Flaminius please post your comment as an answer which is the more suitable answer nowadays! – Ophthalmic

@Flaminius When it is findOne() you don't need limit(1) – Darien

Answer 2

Fast and Simple - One Line Solution

Get 10 latest documents

MySchema.find().sort({ _id: -1 }).limit(10)

Get 10 oldest documents

MySchema.find().sort({ _id: 1 }).limit(10)

In case you want sorting based on some other property i.e. createdAt and get the oldest or latest. It is similar to the above query.

MySchema.find().sort({ createdAt: -1 }).limit(10)  // 10 latest docs
MySchema.find().sort({ createdAt: 1 }).limit(10) // 10 oldest docs

Chronic

I used MySchema.find({...}).sort({createdAt: -1})[0] to get lastest insert and yout query is much faster. Thank you. – Latent

Thanks. Do you know how to get the last 10 recorded sorted by createdAt? – Kilogrammeter

@Kilogrammeter I have updated the answer to answer your question. I haven't tested it, do let me know if it doesn't work or need to improve. – Chronic

@Chronic I meant what if I want the documents that are returned in an unchanged order. Anyway I found the solution, what I did was I sorted them after they are returned using Lodash's sortBy. – Kilogrammeter

Answer 3

for version ~3.8 mongoose

to find the last entry

model.findOne().sort({ field: 'asc', _id: -1 }).limit(1)

or using

model.findOne().sort({ field: -_id }).limit(1)

Flaminius

Isn't limit(1) redundant since you're already using findOne() opposed to find()? – Either

what does -id:-1 do? – Unclothe

Answer 4

collectionName.findOne().sort({$natural: -1}).limit(1).exec(function(err, res){
    if(err){
        console.log(err);
    }
    else{
        console.log(res);
    }
}

This will give you the last document recorded on the database. Just follow the same concept.

Ruralize

This is what I'm looking for. – Equipollent

Answer 5

await Model.find().sort({$natural:-1}).limit(1); //for the latest record
await Model.find().sort({$natural:1}).limit(1); //for the oldest record

This one works for me. using mongodb natural order https://docs.mongodb.com/manual/reference/operator/meta/natural/

Dulin

Answer 6

We have method called sort using that we can able to get first element(old document) which means 1 for sort field or last element(new document) which means -1 for sort field of collection.

Harriet

Answer 7

Here is the answer with async - await

const olderDoc: any = await Model.findOne().sort({ createdAt: 1 }).lean().exec()
console.log('olderDoc', olderDoc)

const newerDoc: any = await Model.findOne().sort({ createdAt: -1 }).lean().exec()
console.log('newerDoc', newerDoc)

Fatness

Answer 8

The best way is to have an async function like that:

async function findLastElement () {
    return await Mymodel.findOne().sort('-_id');
}

this way you get the last element and you ensure reusability.

Irritant