CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Andreas Lyngstad
Andreas Lyngstad

Reputation: 4927

Tracking database over time in ruby on rails

Update

I ended up with a dedicated statistics model and table that gets data with the use of the whenever gem . Look at my own answer for more details.

Orginal question

In my ruby on rails application I want to make a chart in my admin pages that displays the numbers of users on my site over time.

The chart should display the numbers of users on all dates the last year.

If there comes in new users the curve goes up and if users get deleted it goes down.

This would be the JSON result I am after.

[
    {
        "date": "23-01-2013",
        "users": 3201
    },
    {
        "date": "24-01-2013",
        "users": 3451
    },
    {
        "date": "25-01-2013",
        "users": 4351
    },
    {
        "date": "26-01-2013",
        "users": 3950
    },
    {
        "date": "27-01-2013",
        "users": 4150
    }
]

I have considered making a statistics model and add before_create and before_destroy filters in my user model that updates the statistics model.

Is there a better way and maybe a gem that does this? I guess I have not found the solution because I don't know what this is called.

Upvotes: 1

Views: 309

Answers (2)

Andreas Lyngstad
Andreas Lyngstad

Reputation: 4927

The solution was to make a statistics model and table in the database. I use the whenever gem to run a write_stats method on the statistics model every day. It might be a bit heavy, but it gives me great data to pull from.

Here's the schedule.rb for the whenever gem

set :output, "#{path}/log/cron.log"

every :day, at: "11:58 PM" do
  runner "Statistics.write_count"
end

And here's the Statistics model

class Statistics < ActiveRecord::Base
  def self.write_count
    stats = Statistics.new
    stats.users = User.count
    stats.save!
  end
end

The statistics table has timestamps, so it makes queries easy.

Here is one

@stats = Statistics.where(created_at: (Time.now.years_ago(1)..Time.now))

Upvotes: 0

0x4a6f4672
0x4a6f4672

Reputation: 28245

A statistics model is one possibility. If you don't want to add an extra model, you can just make a suitable SQL query, for example to list the number of new users each year in MySQL one can use

SELECT Year(created_at) as 'year', COUNT(id) as 'users' 
       FROM users GROUP BY Year(created_at); 
# or
SELECT DATE_FORMAT(created_at, '%Y') as 'year', COUNT(id) as 'users' 
       FROM users 
       GROUP BY DATE_FORMAT(created_at, '%Y');

The number of new users each month would be similarly using the DATE_FORMAT function of MySQL

SELECT DATE_FORMAT(created_at, '%X.%m') as 'year', COUNT(id) as 'users' 
       FROM users 
       GROUP BY DATE_FORMAT(created_at, '%X.%m');

Upvotes: 1

Related Questions