Random, Previous and Next entries from Active Record models using offset

I have seen a couple of entries floating around on the internet relating to how to obtain a random active record entry or the next or previous active record entries. Many of the examples are actually quite inefficient and although they’ll get the job done they’re probably not best for the poor server. I’m certainly not an expert so any corrections on my methods are more than welcome. Offset is often underused feature by rails developers, in my experience, so I thought I’d post a couple of useful use cases for offset.

Obtaining a random record

One of the ways that seems to appear sometimes is to make use of the MySQL database and some custom ordering to return a list of records in a random order and then just limit the result to the first. This would be done using something like the following.

Entry.find(:all, :limit => 1, :order => "RAND()")

This is not efficient in MySQL. Done this would cause your table to take every record and then generate a random number for it. The time that this takes would therefore be proportional to the number of rows in your table! You also wouldn’t really want to say Entry.find(:all).rand because the entire record set would be returned before a random entry is chosen.

Although it might seem like it would take a little longer as there are two calls my preferred solution would be to add the following method to you model (entry.rb)

  def self.random(options = {})
    count = self.count()
    self.find(:first, options.merge(:offset => rand(count)))
  end

This code will find the count of all of the rows in the table and then use this to choose a record with a random offset. The offset is used to make sure this works when records are deleted. While this could also probably be done in the database with the offset being a select statement any named scopes would not be present on the embedded select and you might be choosing a random number from a larger set than is actually available to the query introducing a bias in the random number chosen. Clearly making two calls carries a tiny amount of risk if the number of items changes though but in the worst case you will simply most likely up returning the most recent entry and this method works across any database as there is nothing specific in here.

Getting the next and previous entries

The use of offset can also be applied to get the next and previous records from the database too. I find using a named scope to act as a class method on the model works great.

named_scope :previous, lambda { |i| {:conditions => ["#{self.table_name}.id  "#{self.table_name}.id DESC"} }
      named_scope :next, lambda { |i| {:conditions => ["#{self.table_name}.id > ?", i.id], :order => "#{self.table_name}.id ASC"} }

This specifies the table name of the model automatically to avoid any conflicts that might occur. I even wrapped this one into a tiny little plugin that can be used to save typing this out for different models. The plugin can be found on github

Summary

This should have shown a few pretty cool uses for offset but I’d also be interested in hearing responses to the approach for obtaining random records. Does anyone do things differently and how is that method better?

Update:

if you need a library to help you pull out more than one random entry then it is worth taking a look at http://github.com/grosser/random_records. The plugin is pretty lightweight but should help make things a little easier.