VideoとCommentを投稿できるUserをそれぞれの投稿への一定期間中のLike数の合計で並べ替え

前提

  • UserはVideoとCommentを持っている。
  • それらに付いたLikeをwhere('created_at > ?', Time.now - 1.days)とかで一定期間に制限してカウントして足し合わせて、その合計が多い順にUserを並べたい。
  • PostgreSQL
  • 以下、例示のために必要な部分だけ抜き出した。

  • /app/models/user.rb

has_many :videos, dependent: :destroy
has many :comments, dependent: :destroy
  • /app/models/video.rb
has_many :video_likes, dependent: :destroy
belongs_to :user
  • /app/models/video_like.rb
belongs_to :video
  • /app/models/comment.rb
has_many :comment_likes, dependent: :destroy
belongs_to :user
  • /app/models/comment_like.rb
belongs_to :comment

実装

# tは「Time.now - 1.days」とか「Time.now- 1.month」とか
User.joins(%|
      left join (
        select
          users.id as user_id,
          coalesce(sum(videos.video_likes_count), 0) as video_likes_count
        from users
        left join (
          select
            videos.id,
            videos.user_id,
            count(video_likes.id) as video_likes_count
          from videos
          left join (
            select
              id,
              video_id
            from video_likes
            where created_at > '#{t}'
          ) as video_likes
          on video_likes.video_id = videos.id
          group by
            videos.id
        ) as videos
        on videos.user_id = users.id
        group by users.id
        order by video_likes_count desc
      ) as user_video on user_video.user_id = users.id
      left join (
        select
          users.id as user_id,
          coalesce(sum(comments.comment_likes_count), 0) as comment_likes_count
        from users
        left join (
          select
            comments.id,
            comments.user_id,
            count(comment_likes.id) as comment_likes_count
          from comments
          left join (
            select
              id,
              comment_id
            from comment_likes
            where created_at > '#{t}'
          ) as comment_likes
          on comment_likes.comment_id = comments.id
          group by
            comments.id
        ) as comments
        on comments.user_id = users.id
        group by users.id
        order by comment_likes_count desc
      ) as c on c.user_id = users.id
    |)
    .select('users.*', '(user_video.video_likes_count + c.comment_likes_count) as count')
    .order('count desc', created_at: :desc)

解説

  • | video_id | video_likes_count |と| comment_id | comment_likes_count |を作って、それらをusersにleft joinし、video_likes_countとcomment_likes_countの和をcountとした。
  • coalesceを使ってlikeされた事のないUserの*_likes_countがnilじゃなくて0になるようにした。

感想

  • joinが多すぎるのでデータが増えたらレスポンスがかなり遅くなりそう。
    • 大学でDBの講義を担当している教員に訊いてみたら「概ねこのやり方でいいと思う。ただしjoinの回数が多すぎるのでマテリアライズドビューを使って途中結果をキャッシュしたほうがいい。」と教えていただいた。
  • 実装し終わってから以下の実装方法もありだと思った。

    • 実は本当に欲しいcreated_atのレンジは「一日」「一週間」「一ヶ月」「制限無し」の4パターンだったので、これらを保存しておくテーブルを作り、6時間毎くらいで更新する。
  • 他の実装方法あったら教えて欲しいです。

参考

qiita.com

www.postgresql.jp