In Rails, I want to show on a page how many new "users" are created each "day".
In regular SQL, I would do something like:
SELECT
DATE(created_at) as day,
COUNT(1) as new_user_count
FROM users
WHERE created_at > '2014-01-01 01:01:01'
GROUP BY day
How can I execute this against my Users table in Rails? Again, I need to show this on a page, so I'm not looking for instructions on the PG GUI.
For specifics, I'm using Heroku and Postgres.
Do it like this:
sql = "SELECT DATE(created_at) as day, COUNT(1) as new_user_count " \
"FROM users WHERE created_at > '2014-01-01 01:01:01' " \
"GROUP BY day"
ActiveRecord::Base.connection.execute(sql)
Aside from a raw SQL solution, you might consider creating a view:
create view daily_user_counts as
SELECT
DATE(created_at) as day,
COUNT(*) as new_user_count
FROM users
WHERE created_at > '2014-01-01 01:01:01'
GROUP BY day;
... and creating a read-only model for it:
class DailyUserCount < ActiveRecord::Base
def readonly?
true
end
end
Then:
DailyUserCount.where("day > ? ", from).order(:day)
