Calculate consecutive days in Rails
In a simple Ruby on Rails application, I am trying to calculate the number of consecutive days posted by a user. So, for example, if I posted each of the last 4 days, I would like to have in my profile "Your current streak is 4 days, continue!" or something like that.
Should I track "stripes" in one of my models, or should I count them elsewhere? Not sure where I should be doing this, or how to properly do this, so any suggestions would be great.
I'm happy to include any code you find useful, just let me know.
source to share
I'm not sure if this is the best way, but here is one way to do it in SQL. Let's look at the following query first.
SELECT
series_date,
COUNT(posts.id) AS num_posts_on_date
FROM generate_series(
'2014-12-01'::timestamp,
'2014-12-17'::timestamp,
'1 day'
) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
ORDER BY series_date DESC;
We are using generate_series
to create a date range from 2014-12-01 to 2014-12-17 (today). Then we make a LEFT OUTER JOIN with our table posts
. This gives us one row for each day in the range, with the number of posts for that day in the column num_posts_on_date
. The results look like this ( SQL Fiddle here ):
series_date | num_posts_on_date ---------------------------------+------------------- December, 17 2014 00:00:00+0000 | 1 December, 16 2014 00:00:00+0000 | 1 December, 15 2014 00:00:00+0000 | 2 December, 14 2014 00:00:00+0000 | 1 December, 13 2014 00:00:00+0000 | 0 December, 12 2014 00:00:00+0000 | 0 ... | ... December, 01 2014 00:00:00+0000 | 0
Now we know that there is a fast every day from December 14-17, so if today is December 17, we know that the current "streak" is 4 days. We could do some more SQL to get for example. the longest line as described in this article , but since we are only interested in the length of the current strip, it will just make a small change. All we need to do is change our query to get only the first date for which num_posts_on_date
is 0
( the SQL Fiddle ):
SELECT series_date
FROM generate_series(
'2014-12-01'::timestamp,
'2014-12-17'::timestamp,
'1 day'
) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1;
And the result:
series_date --------------------------------- December, 13 2014 00:00:00+0000
But since we really want the number of days since the last day without messages, we can do that in SQL too ( SQL Fiddle )
SELECT ('2014-12-17'::date - series_date::date) AS days
FROM generate_series(
'2014-12-01'::timestamp,
'2014-12-17'::timestamp,
'1 day'
) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1;
Result:
days ------ 4
There you go!
Now, how do we apply it to our Rails code? Something like that:
qry = <<-SQL
SELECT (CURRENT_DATE - series_date::date) AS days
FROM generate_series(
( SELECT created_at::date FROM posts
WHERE posts.user_id = :user_id
ORDER BY created_at
ASC LIMIT 1
),
CURRENT_DATE,
'1 day'
) AS series_date
LEFT OUTER JOIN posts ON posts.user_id = :user_id AND
posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1
SQL
Post.find_by_sql([ qry, { user_id: some_user.id } ]).first.days # => 4
As you can see, we added a condition to limit the results by user_id and replaced our hard-coded dates with a query that gets the date of the first user post (a sub-select within the function generate_series
) for the start of the range and CURRENT_DATE
for the end of the range.
This last line is a little funny because it find_by_sql
will return an array of Post instances, so you need to call days
on the first one from the array to get the value. Alternatively, you can do something like this:
sql = Post.send(:sanitize_sql, [ qry, { user_id: some_user.id } ])
result_value = Post.connection.select_value(sql)
streak_days = Integer(result_value) rescue nil # => 4
Inside ActiveRecord, it can be made a little cleaner:
class Post < ActiveRecord::Base
USER_STREAK_DAYS_SQL = <<-SQL
SELECT (CURRENT_DATE - series_date::date) AS days
FROM generate_series(
( SELECT created_at::date FROM posts
WHERE posts.user_id = :user_id
ORDER BY created_at ASC
LIMIT 1
),
CURRENT_DATE,
'1 day'
) AS series_date
LEFT OUTER JOIN posts ON posts.user_id = :user_id AND
posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1
SQL
def self.user_streak_days(user_id)
sql = sanitize_sql [ USER_STREAK_DAYS_SQL, { user_id: user_id } ]
result_value = connection.select_value(sql)
Integer(result_value) rescue nil
end
end
class User < ActiveRecord::Base
def post_streak_days
Post.user_streak_days(self)
end
end
# And then...
u = User.find(123)
u.post_streak_days # => 4
The above is untested, so it will likely take a while to get it working, but I hope it points you in the right direction.
source to share
I would create two columns in a custom model. "streak_start" and "streak_end", which are timestamps.
The inferred messages belong to the user.
Publishing model
after_create :update_streak
def update_streak
if self.user.streak_end > 24.hours.ago
self.user.touch(:streak_end)
else
self.user.touch(:streak_start)
self.user.touch(:streak_end)
end
end
Personally, I would write it like this:
def update_streak
self.user.touch(:streak_start) unless self.user.streak_end > 24.hours.ago
self.user.touch(:streak_end)
end
Then define the sequence of users.
User model
def streak
# put this in whatever denominator you want
self.streak_end > 24.hours.ago ? (self.streak_end - self.streak_start).to_i : 0
end
source to share
I believe Andrew's answer will work. Admittedly, I can overestimate this solution, but if you want a SQL-centric solution that doesn't need to maintain columns of columns, you can try something like this:
SELECT
*, COUNT(diff_from_now)
FROM
(SELECT
p1.id,
p1.user_id,
p1.created_at,
(DATEDIFF(p1.created_at, p2.created_at)) AS diff,
DATEDIFF(NOW(), p1.created_at) AS diff_from_now
FROM
posts p1
LEFT JOIN (SELECT
*
FROM
posts
ORDER BY created_at DESC) p2 ON DATE(p2.created_at) = DATE(p1.created_at) + INTERVAL 1 DAY
WHERE
(DATEDIFF(p1.created_at, p2.created_at)) IS NOT NULL
ORDER BY (DATEDIFF(p1.created_at, p2.created_at)) DESC , created_at DESC) inner_query
GROUP BY id, sender_id, created_at, diff, diff_from_now, diff_from_now
HAVING COUNT(diff_from_now) = 1
where user_id = ?
In short, the innermost query calculates the date difference between this post and the next consecutive post, and also calculates this post difference from the current date. The outer query then filters everything where the sequence of date differences does not increase by one day.
Note: . This solution has only been tested in MySQL and while I see that you have specified Postgres as your database, I don’t have time right now to change the functions correctly to the ones used by Postgres. I'll return this answer shortly, but I thought it might be helpful to see this sooner rather than later. This note will also be removed when this post is updated.
You must execute this as raw SQL. This can also be converted to Active Record, which I will most likely do when I update this post.
source to share