Selecting random record in rails

Question

I am trying to randomly select a record from the database and display it in my view. The catch is I don't want the selected random record to repeat until all the records are selected at least once. For instance, let's say my table has 10 records, so I want to pick any one random record and make sure that, that randomly selected record is not selected again until the other nine records are selected at least once.

snippet:

    offset = rand(Quote.count)

    @qotd = Rails.cache.fetch( "term", :expires_in => 6.seconds){ Quote.offset(offset).first }

score 2 · Answer 1 · answered Sep 19 '17 at 19:30

2

If you need to track usage then what you need is some kind of sequence column, populate that with random values, and NULL them out as you use them up. When they're all NULL, re-seed with random values and do it all over again.

answered Sep 19 '17 at 19:30

tadman

208,517
23
234
262

score 0 · Accepted Answer · answered Sep 19 '17 at 20:22

You'll need a separate spot to store the records being displayed already outside of your current process. The process might die or you will have multiple processes running (if it is meant for production purposes, that should definitely be the case).

As opposed to what tadman proposed I wouldn't recommend storing the sequence in the db as this will make adding new records more complicated.

Instead, I would add a simple displayed (boolean) column (or separate table) and rely on what has been proposed in a different thread but flavored to your specific use case. So the query might be like this:

@qotd = Quote.where(displayed: false).order("RAND()").limit(1)

and of course a

@qotd.update_attribute :displayed, true

later on.

The displayed column for all records will have to be reset if no candidate is found. All this, (query, update and reset) should probably happen within a single method.

`order("RAND()")` will set fire to your database server if that table is reasonably large... or you're hitting it that query often enough. — Grocery, Sep 21 '17 at 01:23

score 0 · Answer 3 · answered Sep 19 '17 at 20:52

My suggestion is to add a column like "display_count" to your table. When you want to get a random item to display, you can simply issue a query like the following:

quote = Quote.where(
  display_count: Quote.select("MIN(display_count)")
  ).order("RANDOM()")
quote.update_attribute(:display_count, quote.display_count + 1)

That way you always select an item that has been displayed the least, and increment it after you retrieve it. That should (almost) guarantee that every time you run this code, you get a new item.

Selecting random record in rails

3 Answers3