I'm not sure if it's the best way, but here's one way to do it in SQL. First, take a look at the following query.
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 use generate_series to generate a range of dates starting on 2014-12-01 and ending 2014-12-17 (today). Then we do a LEFT OUTER JOIN with our posts table. This gives us one row for every day in the range, with the number of posts on that day in the num_posts_on_date column. The results looks 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 there's a post on every day from Dec. 14–17, so if today's Dec. 17 we know the current "streak" is 4 days. We could do some more SQL to get e.g. the longest streak, as described in this article, but since we're only interested in the length of the "current" streak, it'll just take a small change. All we have to do is change our query to get only the first date for which num_posts_on_date is 0 (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 actually want the number of days since the last day with no posts, 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 to apply it to our Rails code? Something like this:
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 restrict results by user_id, and replaced our hard-coded dates with a query that gets the date of the user's first post (the sub-select inside the generate_series function) for the beginning of the range and CURRENT_DATE for the end of the range.
That last line is a little funny because find_by_sql will return an array of Post instances, so you then have to call days on the first one in the array on to get the value. Alternatively, you could 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
Within 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'll likely take some fiddling to make it work, but I hope it points you in the right direction at least.
