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時間毎くらいで更新する。
他の実装方法あったら教えて欲しいです。