Random in ActiveRecord

I was trying to make a Quote model that could feed my webapp with
random quotes. I posted about this at http://nunojob.wordpress.com/2008/02/08/random-in-ruby-on-rails

After creating the model i entered ./script/console and tried

Quote.find(:random)

This didn't work so I looked for another solution. Some of those
where:

Quote.find(:all)[rand(Quote.count)] # Works but fetches all quotes and does 2 sql statetments
Quote.find_by_id rand(Quote.count) + 1 # Doesn't work if somebody deletes a quote and uses 2 sql statements.
Quote.find(:first, :order => "RAND()") # RAND is for mysql only. There no way to pass the correct sql to everyadapter

After this my friend António Medeiros made a way to do this, for my
model:

require 'rubygems'
gem 'activerecord'
require 'activerecord'

ActiveRecord::Base.establish_connection(
:adapter => "mysql",
:host => "localhost",
:username => "utilizador",
:password => "password",
:database => "teste"
)

class Quote < ActiveRecord::Base

  def self.find(*args)
    if args.first.to_s == "random"
      super(:first, :order => self.random_command)
    else
      super
    end
  end

  def self.random_command

    case self.connection.class
      when ActiveRecord::ConnectionAdapters::MysqlAdapter
       'RAND()'
      else
        # Other ways to do RAND for diferent adapters.
        'RANDOM()'
    end
  end
end

puts Quote.find(:random).quote

The next step seemed to be suggesting a patch to rails core. However
I'm still a rails rookie and don't have that necessary knowledge to
understand all of that code. Can you tell me if this is already on
rails core? Can someone add it please. It seems rather easy.

Thanks

The next step seemed to be suggesting a patch to rails core. However
I'm still a rails rookie and don't have that necessary knowledge to
understand all of that code. Can you tell me if this is already on
rails core? Can someone add it please. It seems rather easy.

The problem with this is that order by RAND is extremely inefficient
as it has to evaluate RAND() for every row in the table. I'm hesitant
to stick something this ... dangerous into the core framework itself.

One of the comments in my blog suggested a way to improve the
performance in mysql (http://jan.kneschke.de/projects/mysql/order-by-
rand/).

I guess that there are other tweaks for other database systems (like
db2, oracle, postgre, sqlite). So in the core we could have the
tweaked fast versions of random for each of the dbms. Then the user
would only need to

Quote.find(:random)

And rails would do the magic :slight_smile:

I read Jan's entry as well a while ago, and it's very interesting. However, it's also pretty laborious and not trivial to implement across different databases. So I feel this is a good candidate for a plugin but certainly not for core, at least at this point.

Cheers,
//jarkko

Nuno, I support this in a form of a plugin. It is definitely useful. But, because of various approaches needed because of different databases (and a lot of people disagreeing which selects are optimized), I don’t think it will ever be fit for core (as Koz suggests).

For tables that are have less rows than thousands, I think that selecting (and caching) the list of IDs always performs well. Then, use rand from Ruby to pick a random record. I don’t think you have a thousand quotes :slight_smile:

When you release a plugin, there will be people who will contribute more optimized solutions based on their needs.

  • M

Another thought on random ordering: I'd like to see how this is
testable without sticking to mocks :slight_smile:

Nuno,

I blogged (http://daniel.collectiveidea.com/blog/2007/5/17/the-road-to-
randomness) about this a while ago and got some great feedback from
Mislav (read the comments).

I made a plugin (though rarely use it) and if you want to improve it,
be my guest!

I tend to agree that this shouldn't be in core. The issues are a bit
sticky and you could easily make a DB crawl. However, in some cases
it may be ok.

git: http://github.com/collectiveidea/random_finders
svn: http://source.collectiveidea.com/public/rails/plugins/random_finder

Cheers,

-Daniel