MongoDB return 6 random rows from collection

Question

Hi I want to show random 6 rows from a collection. each row as a timestamp so I could use that but my question is how do I return only 6 rows from the collection and make it random

here is a sample of my collection - I use PHP

{
   "age": "2",
   "breed": "Bengal",
   "dislikes": "Dislikes being patted by people",
   "likes": "Like to purr and get headbutts. Sleeps on our bed, with Woody our dog, and also comes in for food at 6pm, loves Tin fish and is known to meow quite lo [...]",
   "lost": true,
   "pet_lost_date": NumberInt(1361366445),

   "type": "cat" 
}

I saw this db.items.find().skip(randonNumberHere).limit(1); - MongoDB: Pulling multiple random documents from a collection

but I did not understand it, all i understand from that is the find() which finds everything skip() which skips a number of rows and limit() which is how many get returned.

However My question is more about getting all the lost pets and random them and only showing 6

public function lost_pets($no){
        $collection = static::db()->ipet_mypet;
        $pet = $collection->find(array('lost': true, '$where'=> function(){var randomNumber=Math.random(); return this.random>=randomNumber || this.random>randomNumber })).sort(array('pet_lost_date'=> 1)).limit(6);
    }

Amith Koujalgi · Answer 1 · 2013-02-21T19:44:31.593

0

You could use this:

db.collection.find({'lost': true, $where: function(){var randomNumber=Math.random(); return this.pet_lost_date>=randomNumber || this.pet_lost_date>randomNumber }}).next();

Find({'lost':true}) fetches the documents with field 'lost': true.

The $where clause returns a DBCursor, which points to a particular document. By calling "next()", we obtain the next document pointed by the cursor. So we fetch one random document at a time.

edited Feb 21 '13 at 19:44

answered Feb 21 '13 at 18:31

Amith Koujalgi

10,186
2
17
22

2

The problem is that it doesn't really give you "6 random rows". It gives you 6 sequential documents starting from a random offset. This means that the chance of picking 6 identical documents twice = the chance of picking the same offset twice – Colin M Feb 21 '13 at 18:37
but that will still show the lost results in order. what I want to do is random the lost that are true say I have 300 lost:true i want to show 6 random ones – RussellHarrower Feb 21 '13 at 18:37
@RussellHarrower To make this slightly more random, you could select, for example, 12 documents back from Mongo (instead of 6). Then, in PHP, you could randomly select from those 12. Then, even if you do manage to randomly pick the same offset (and thus get the same 12 back from Mongo) - it's HIGHLY unlikely that your PHP randomization will also pick the same subset of 6. – Colin M Feb 21 '13 at 18:42
@ColinMorelli could you please show an example - i think i understand you, but not 100% sure – RussellHarrower Feb 21 '13 at 18:46
You can use this in PHP: $c->find()->limit( -1 )->skip(mt_rand( 0, $c->count() ))->getNext(); – Amith Koujalgi Feb 21 '13 at 18:47
And for count of docs, you can use db.collection.find({'lost':true}).count(); – Amith Koujalgi Feb 21 '13 at 18:47
Or more efficient code is issuing this query in the server-side: db.collection.find({ $where : function(){ var randomNumber=Math.random(); return this.random>=randomNumber || this.random>randomNumber }}).limit(1) – Amith Koujalgi Feb 21 '13 at 18:50
@AmithKoujalgi That's not faster at all. The first query was (I assume) performing an index-search on the `lost` attribute and returning a random-offset with a limit of 6. Your query now requires a full collection scan across the entire data set, and also doesn't guarantee any number of results. – Colin M Feb 21 '13 at 18:52
@AmithKoujalgi could you show your example as an answer hard to understand in comments – RussellHarrower Feb 21 '13 at 18:52
@RussellHarrower: I have edited my answer, you could try that out and let me know if it works. Fingers crossed! – Amith Koujalgi Feb 21 '13 at 18:55
You can actually write an application logic of your own in the $where block to serve you with whatever your logic demands. – Amith Koujalgi Feb 21 '13 at 19:02
I get told that theres an error ill past my code (your code) above – RussellHarrower Feb 21 '13 at 19:03
I'm not familiar with issuing a query in PHP, but the above query works just fine when you run from mongo shell. And the limitation of the query is that it will fetch just one random document. – Amith Koujalgi Feb 22 '13 at 03:29

score 0 · Answer 2 · answered Mar 09 '15 at 10:46

0

According to the way I see it logically, the easy way is to save records with a random number, then sort by that number when reading from the db.

answered Mar 09 '15 at 10:46

codeartist254

1

score 0 · Answer 3 · edited Nov 22 '15 at 09:21

Let say you have 20 records you want to randomly fetch 5 records

We will generate a random skip value between 0 to 20 - limit value (5) = 15.
With this we are sure to return 5 records even if the random skip value starts from 15.
We can also force the skip value to be zero if its becomes negative after we substract the random value from the total records

Example code:

$total_records = $collection->count(); <br>
$limit = 5; <br>
$skip = mt_rand(0, $total_records);<br>
$collection->find()->skip($skip < 0 ? 0 : $skip)->limit($limit);

MongoDB return 6 random rows from collection

3 Answers3