by Matt Polito on June 4, 2013

Ordering records returned from ActiveRecord in a random manner is on the chopping block today.

Scenario: I want to return 5 random users

How would we go about solving this? Well maybe a first pass would look something like this?

![Random][header_image]

User.all.shuffle[0..4]

So on this first pass we are grabbing all User records, shuffling them around, and then grabbing the first 5 records. This solves the problem but why would we do something different?

First we are returning all User records just to return 5! Doing something like this is almost always a bad idea especially when dealing with User type records. They are likely to have a high record count in your application. When you return them all, each one is taking up memory just for you to return 5.

Let's alleviate this problem a bit with another solution.

ids = User.pluck(:id).shuffle[0..4]
User.where(id: ids)

Now we are making an extra database call in favor of not returning a bunch of unnecessary ActiveRecord objects. First we [pluck][pluck_article] the id column which returns an id for all User records. Then we [shuffle][shuffle_doc] that array and choose the first 5 records.

Well this is pretty good, we are saving our memory for the records we actually want. This again solves our problem but it's a bit more to look at (with the extra DB call).

We can alleviate this problem a bit with yet another solution.

# postgresql
User.limit(5).order("RANDOM()")

Let's review what is happening here. We are querying for User records ordered randomly and limiting the return set to 5 results. Our original problem to solve was: I want to return 5 random users. Not only have we written code to solve this issue but the code written is expressed very closely to what we wanted if it were read out loud.

What changed? The main difference is the RANDOM() passed to order. For those who think this looks a bit odd, this is a database specific function. You can pass all sorts of SQL fragments to ActiveRecord that just get joined in to make a comple SQL statement. This is just another fragment. The call being made actually looks like this:

SELECT * FROM users ORDER BY RANDOM() LIMIT 5

Now I know what you may be thinking... "ActiveRecord is database agnostic! Why would you bind yourself to a specific implementation!?" The simple answer is yes, ActiveRecord is database agnostic and yes it is great. It gives the developer the option to choose which database is best to solve your specific problems. Now just think that once your database choice has been made, how often if ever are you going to make a change to another? Databases are very good at what they were made to do... let them be good at it. Don't be afraid to use database specific features and functions to improve your development. This particular example has been granted to us by [PostgreSQL][postgresql], which is my go-to DB of choice.

Not using PostgreSQL? This similar functionality can be had by most databases. Consult with your documentation. Here is a [MySQL][mysql] example to show how the same functionality can be had:

# mysql
User.limit(5).order("RAND()")

[mysql]: http://www.mysql.com/ [pluck_article]: http://blog.hashrocket.com/posts/rails-quick-tips-activerecord-calculations-pluck [postgresql]: http://www.postgresql.org/ [shuffle_doc]: http://ruby-doc.org/core-2.0/Array.html#method-i-shuffle [header_image]: http://f.cl.ly/items/1v143V1U2p1d1L3X0V00/random.jpg