Displaying non repeating random records

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])
Copy

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])
Copy

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])
Copy

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
Copy

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