CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordrandome-commerce
Jamon Holmgren
Jamon Holmgren

Reputation: 24412

Postgres - Randomize results once per day

I'm building a store and would like to randomize a product page, but only change it once per day.

I know that a randomizer with a seed number can return consistent results, so perhaps using the current day as a seed would work.

Caching would also work, or storing the results in a table.

What would be a good way to do this?

Upvotes: 1

Views: 460

Answers (3)

Jamon Holmgren
Jamon Holmgren

Reputation: 24412

I found a different way to accomplish this that will also let me use the will_paginate gem and have fresh info when the products are updated.

I added a sort_order long integer to the table. Then, once a day, I will run a query to update that field with random numbers. I'll sort that field.

Conceptual Rails code:

# Pulling in the products in the specified random order
def show
  @category = Category.where(slug: params[:id].to_s).first
  if @category
    @random_products = @category.products.order(sort_order: :desc) # desc so new products are at the end
  end
end

# Elsewhere...
def update_product_order
  products = Product.order("RANDOM()").all
  order_index = 0
  products.each do |p|
    product.sort_order = order_index
    product.save! # this can be done much more efficiently, obviously
    order_index += 1
  end
end

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658302

Create a materialized view. That's just another table in current PostgreSQL, updated with the results of a query. I might install a cron job that triggers the refill. You can have any amount of caching on top of that.

The upcoming Postgres 9.3 will have a new feature.
More on materialized views in the Postgres wiki.

For a fast method to pull random rows you may be interested in this related question:
Best way to select random rows PostgreSQL

Upvotes: 2

Josh
Josh

Reputation: 8596

You definitely want to cache the results. Sorting things randomly is slow (especially in large datasets). You could have a cron job that ran every night to clear out the old cache and pick new random products. Page cache is best if you can pull that off, but a fragment cache would work fine too.

Upvotes: 1

Related Questions