Rails: get a list of random items from MySQL database

Question

I have a number of tags for each post. (Very similar to SO). I want 20 random items, non-repeating.

I know that I could use Tags.all.rand (And repeat 10 times) however, this won't guarantee uniqueness.

And I know I could use a SQL Query but since my development environment use sqlite as the db and MySQL in production, ORDER by RAND won't work for both.

Aside from having two different queries (Dev & Prod) what else can I do?

Thanks

Answer 1

Why are you using two different databases for development and production? This may lead to problems further down the line. Take for example what happened to us recently, one of our devs used MySQL and was using a special MySQL-only LIKE syntax which didn't work on our PostgreSQL production database.

Morale of the story: If you're using something different for development, test and/or production: you're doing it wrong.

Next: To get the random items in MySQL you can do: Tag.all(:order => "RAND()", :limit => 10).

Answer 2

Ryan's answer does it, but comes with a gotcha. If your dataset is very big, :order => "RAND()" is impractical (the query will run very slowly). There are two alternate methods: get first all the IDs of your items, select 20 at random and then fetch those rows, or get the number of items in the db, select one with a random offset, and repeat (choose the offsets first to guarantee no duplicates).

Read more about this in SQL Antipatterns book, being this chapter a freebie there.

BTW: If using Ruby 1.8+ or Rails 2.3+ (I believe) there is the method Array#sample that will let you choose n rand elements without repetition, as in your original approach.

Answer 3

You can select a random sample of 20 tags using:

Tag.all.sort_by {rand}[0..19]

(Thanks to chubas for this simple solution!)

The downside of this is that it will be inefficient over large data sets. You could improve this by selecting only the IDs:

Tag.all(:select => :id).map(&:id).sort_by{rand}[0..19]