CODEWITHSUNDEEP
ruby-on-railssqlite
Scott Myers
Scott Myers

Reputation: 202

Grab Random record from SQL Database and display it

I'm on Rails 4 using SQLite, and I have a database of 75,000 quotes. I would like to grab a random record (quote) from this database and display it for users to see. I've looked around but no method I have searched around here has worked yet. Here's code that grabs the first record and displays it:

<% Quote.limit(1).all.each do |quote| %>
<%= quote.quote %> :: <%= quote.author %> :: <%= quote.genre %>

I have also tried the following method, but this returns a long alphanumeric value rather than the quote.

offset = rand(Quote.count)
rand_record = Quote.offset(offset).first

Thanks!

Upvotes: 0

Views: 84

Answers (1)

Philip Hallstrom
Philip Hallstrom

Reputation: 19889

Do not order by random() in your SQL call. It's extremely inefficient as your database has to call the random function for each row, assign it a value, then sort on it. As the number of rows grows this becomes very slow.

Your second method should work and is a much better solution. I tried it with my own database and it works. rand_record will be a Quote object -- not the quote string itself. I would do this:

In your controller action:

random_offset = rand(Quote.count - 1)
@random_quote = Quote.offset(random_offset).first

In your view:

<%= @random_quote.quote %> :: <%= @random_quote.author %> :: <%= @random_quote.genre %>

The reason we subtract one from the count is that if we choose an offset that is the same as the number of Quote records then our call to fetch will be nil as the query's offset is one too many.

Upvotes: 2

Related Questions