Random in ActiveRecord

[Nuno Job]

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

[Michael Koziarski]

> 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.

--
Cheers

Koz

[Nuno Job]

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 :)

[Jarkko Laine]

On 9.2.2008, at 0.37, Nuno Job wrote:

>
> 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 :)

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

--
Jarkko Laine
http://jlaine.net
http://dotherightthing.com
http://www.railsecommerce.com
http://odesign.fi

[Mislav Marohnić]

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 :)

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

- M

[Michael Klishin]

On 09/02/2008, Michael Koziarski <mic...@koziarski.com> wrote:
> 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.

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

--
MK

http://novemberain.com

[Daniel Morrison]

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

On Feb 8, 2:17 pm, Nuno Job <nunojobpi...@gmail.com> wrote:
> I was trying to make a Quote model that could feed my webapp with

> random quotes. I posted about this athttp://nunojob.wordpress.com/2008/02/08/random-in-ruby-on-rails