Displaying non repeating random records

Neeraj Singh

By Neeraj Singh

on November 13, 2013

For one of our clients we need to display random records from the database. That's easy enough. We can use random() function.

1Batch.published_and_featured.order('random()')
2                            .paginate(per_page: 20, page: params[:page])

Here we are using PostgreSQL database but ,I believe, above query will also work on MySQL.

The problem here is that if the user clicks on next page then we will try to get next set of 20 random records. And since these records are truly random, sometimes the user might see the records which has already been seen in the first page.

The fix is to make it random but not truly random. It needs to be random with a seed.

Fix in MySQL

In MySQL we can pass seed directly to random() function.

1Batch.published_and_featured.order('random(0.3)')
2                            .paginate(per_page: 20, page: params[:page])

Fix in PostgreSQL

In PostgreSQL it is a little more cumbersome. We first need to set seed and then the subsequent query's usage of random() will make use of seed value.

1Batch.connection.execute "SELECT setseed(0.2)"
2Batch.published_and_featured.order('random()')
3                            .paginate(per_page: 20, page: params[:page])

Set seed value in before_action

For different user we should use different seed value and this value should be random. So we set the seed value in before_action.

1def set_seed
2  cookies[:random_seed] ||= SecureRandom.random_number
3end

Now change the query to use the seed value and we are all set.

Stay up to date with our blogs. Sign up for our newsletter.

We write about Ruby on Rails, ReactJS, React Native, remote work,open source, engineering & design.