78

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 );
    }
  );
}
askmike
  • 1,900
  • 4
  • 21
  • 27

8 Answers8

146

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) { ... });
Kaspar Lee
  • 5,446
  • 4
  • 31
  • 54
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
  • 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? – askmike Sep 17 '12 at 21:18
  • 1
    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`](https://github.com/caolan/async#parallel) for doing that sort of thing cleanly. – JohnnyHK Sep 17 '12 at 21:22
  • 1
    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 – atom2ueki Oct 23 '14 at 08:01
  • 20
    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)` – atom2ueki Oct 23 '14 at 08:39
  • 1
    @atom2ueki - 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. – Chanoch Dec 16 '15 at 12:39
  • Unsupported projection option: sort: { created_at: -1 } when i use this – ingrid Aug 13 '16 at 22:30
  • @ingrid You may have forgotten one of the two `{}` parameters that must precede that parameter for it to be interpreted as query options. – JohnnyHK Aug 13 '16 at 23:58
  • 1
    sorting on the `_id` is faster than sorting on `created_at` (because of indexing) – Maxim Jul 18 '17 at 13:39
  • @atom2ueki please post your comment as an answer which is the more suitable answer nowadays! – Amin Jafari Oct 19 '17 at 17:28
  • @atom2ueki When it is `findOne()` you don't need `limit(1)` – Ahmad Apr 25 '20 at 08:21
62

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
LuisEnMarroquin
  • 1,609
  • 1
  • 14
  • 26
WasiF
  • 26,101
  • 16
  • 120
  • 128
  • 2
    I used `MySchema.find({...}).sort({createdAt: -1})[0]` to get lastest insert and yout query is much faster. Thank you. – Ali Hesari Mar 15 '19 at 14:50
  • Thanks. Do you know how to get the last 10 recorded sorted by `createdAt`? – philosopher Nov 07 '20 at 04:01
  • @philosopher 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. – WasiF Nov 07 '20 at 09:26
  • 1
    @WasiF 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. – philosopher Nov 07 '20 at 09:43
16

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)
atom2ueki
  • 837
  • 4
  • 15
  • 32
5
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.

Fabio do Nascimento
  • 51
  • 1
  • 1
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/

Citra20
  • 61
  • 2
  • 5
1

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.

KARTHIKEYAN.A
  • 18,210
  • 6
  • 124
  • 133
1

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.

yahya benzha
  • 46
  • 5
0

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)
LuisEnMarroquin
  • 1,609
  • 1
  • 14
  • 26