Page visits statistics

Question

I need to implement product pages visits statistics (by days). How better to do this in Rails?

Answer 1

The quick and dirty way to build something like this within Rails is to make a tracking table:

create_table :product_views do |t|
  t.integer :product_id, :null => false
  t.date :viewed_at, :null => false
  t.integer :views, :null => false, :default => 1
end

add_index :product_views, [ :product_id, :viewed_at ], :unique => true
add_index :product_views, :viewed_at

The unique index will mean you have one entry per product per day. There will be missing entries for days with no views for that product. It also means you can use the ON DUPLICATE KEY feature of MySQL if that's your back-end database.

You can populate this using a single SQL call:

class Product
  def self.record_view!(product_id)
    connection.execute(
      sanitize_sql([
        "INSERT INTO product_views (product_id, viewed_at)
          VALUES (?, CURRENT_DATE())
          ON DUPLICATE KEY UPDATE views=views+1",
        product_id
      ])
    )
  end

  def record_view!
    self.class.record_view!(self.id)
  end
end

This can be called at any time and will add 1 to your counter table. You can report on this quite easily using standard SQL.