:order_by => ["RAND()"]

rubyonrails-talk
1

Hi there,

I'm trying to grab a random row out of a database and I keep getting the error:

Couldn't find Reason with ID=random

Here's my controller:

@reason = Reason.find(:first, :order => ["RAND()"])

I think that's right, right? I know that RAND() is an expensive operation, but it's a small table so I'm not too worried.

Thanks!

2

Reason.find(Reason.find(:all, :select => :id).map(&:id).rand)

Enjoy.

3

or…

@reason = Reason.find(:all, :order => “RAND()”, :limit => 1)

(you might need to do a .first on the result set)

Jodi

4

Hmm... I tried what you have to a T (including the .first) and no luck. Weird! I'll keep trying stuff.

Jodi Showers wrote:

5

Hey Dave -

Hmm... I tried what you have to a T (including the .first) and no luck. Weird! I'll keep trying stuff.

Jodi Showers wrote:

the error:

Couldn't find Reason with ID=random

Here's my controller:

@reason = Reason.find(:first, :order => ["RAND()"])

I think that's right, right? I know that RAND() is an expensive operation, but it's a small table so I'm not too worried.

or..

@reason = Reason.find(:all, :order => "RAND()", :limit => 1)

(you might need to do a .first on the result set) Jodi

I'm doing that with rails 1.2.3 and mysql 5 (with a few :conditions and :includes thrown in as well).

Jodi

6

Thanks for the patience. I figured it out, and it was an unrelated routing error. Grr. :slight_smile:

Jodi Showers wrote:

7

You could also try using the find_random plugin: http://www.railslodge.com/plugins/964-find-random

I have not tried using it yet, but it looks well-executed. Has anyone tried using the find_random plugin with has_finder?

Jason Arora

8

For grabbing a random row, ‎`ORDER BY RAND()` works on tiny tables but scales poorly because it sorts the whole result. A practical alternative is to pick a strategy based on DB and table size. I use a small gem, RandomRails, that integrates with ActiveRecord and does exactly that: on PostgreSQL it uses ‎`TABLESAMPLE BERNOULLI` for large tables; on MySQL/SQLite it uses an efficient offset-based approach. Usage stays simple: ‎`Reason.random` or ‎`Reason.random(count: 1)`, and it composes with scopes like ‎`Reason.where(active: true).random`. If you’re curious, the implementation and benchmarks are open source here: https://github.com/the-rubies-way/random-rails :up_right_arrow: