How to get 5 random rows from a model

[bvallelunga]

I have a users model and I want to be able to get 5 random rows. @spartan563 do you have any suggestions on how to do this?

[dxg]

There was some discussion here and here.

There is a chainable orderSql statement which accepts raw SQL. One could combine it with limit().
It's not a performant solution, but there is more discussion on the internets.

[bvallelunga]

after doing some research I was able to use this

models.users.all().limit(5).orderRaw("rand()").run

[bvallelunga]

im closing this issue, thanks again for your help.

[notheotherben]

As dxg said, your solution works but is horrible on performance as it first sorts all rows in the db using random nunbers and only then selects elements. If your database server is close then you are probably better off using skip+limit and making multiple queries - will vastly reduce your database server load

[bvallelunga]

@spartan563 could you give me an example of how to do the skip + limit?

[notheotherben]

I believe the idea is to do a count(), and then use native Math.random() * count as your skip, and then depending on how random something needs to be you can either set limit as the number of elements you require or limit: 1 and n queries to retrieve the records you need.

Some pseudocode (sync design, would need to make it async for Node)

var count = db.model.count(...);
var models = [];
while(models.length < n)
    models.push(db.model.find().skip(Math.random() * count).limit(1));

[dresende]

orm-random - adds Model.getRandom and Model.findRandom using the technique @spartan563 described

[bvallelunga]

thank you @dresende