ruby-on-rails – 计算在Rails中发布的连续天数

我不确定它是否是最好的方法,但这是在SQL中实现它的一种方法.首先,看看以下查询.

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;

我们使用generate_series生成从2014-12-01开始到2014-12-17(今天)结束的日期范围.然后我们用posts表进行LEFT OUTER JOIN.这为我们在范围内的每一天提供了一行,当天在num_posts_on_date列中有帖子数.结果看起来像这样(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

现在我们知道从12月14日到17日每天都有一个帖子,所以如果今天的12月17日我们知道当前的“连胜”是4天.我们可以做更多的SQL来获得最长的连胜纪录,如described in this article,但由于我们只对“当前”连胜的长度感兴趣,所以只需稍作改动.我们所要做的就是更改查询以仅获取num_posts_on_date为0的第一个日期(SQL Fiddle)：

SELECT series_date
FROM generate_series(
       '2014-12-01'::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;

结果如下：

series_date
---------------------------------
 December,13 2014 00:00:00+0000

但是因为我们实际上想要从最后一天起没有帖子的天数,我们也可以在SQL中做到这一点(SQL Fiddle)：

SELECT ('2014-12-17'::date - series_date::date) AS days
FROM generate_series(
       '2014-12-01'::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;

结果：

days
------
    4

你去！

现在,如何将它应用于我们的Rails代码？像这样的东西：

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

正如您所看到的,我们添加了一个条件来限制user_id的结果,并用一个查询替换我们的硬编码日期,该查询获取用户的第一个帖子的日期(generate_series函数内的子选择)作为范围的开头和CURRENT_DATE为范围的结尾.

最后一行有点搞笑,因为find_by_sql将返回一个Post实例数组,因此您必须在数组中的第一个上调用days来获取值.或者,您可以这样做：

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

在ActiveRecord中,它可以变得更干净：

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
          ),'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

以上是未经测试的,因此可能需要一些摆弄才能使其正常工作,但我希望它至少能指出正确的方向.