ruby-on-rails - Rails4，获取所有(多级)评论及其作者和作者评级

Question

拥有模型:

class Movie < ActiveRecord::Base   
  has_many :comments
  has_many :ratings
end

# self join to unable multilevel comments
class Comment < ActiveRecord::Base
  belongs_to :upcomment, class_name: "Comment"
  belongs_to :user
end

class Rating < ActiveRecord::Base
  belongs_to :movie
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :ratings
  has_many :comments
end

当我不想获得所有评论及其回复和评论/回复作者时，我会执行以下查询:

@comments = Comment
  .where('comments.movie_id = ? AND comments.upcomment_id IS NULL', params[:movie])
  .includes([:user, :replies => :user])

Q1:但是我应该如何修改此查询以包含用户对每个评论和回复的评分？

我的尝试

为了简单起见，现在我将跳过回复。我在 .includes 和 where 子句中添加了评级，如下所示:

@comments = Comment
  .where('comments.movie_id = ? AND comments.upcomment_id IS NULL', params[:movie]) 
  .includes([:user => :ratings])
  .where(ratings: { movie_id: params[:movie] })

它有效，但它只返回作者有评级的那些评论。当我查看 Rails 生成的 SQL 时，我注意到 '"ratings"."movie_id"= ?'是(可能)在错误的地方:

SELECT [...]
LEFT OUTER JOIN "users" "users" ON "users"."id" = "comments"."user_id" 
LEFT OUTER JOIN "ratings" "users_ratings" ON "ratings"."user_id" = "users"."id" 
WHERE (comments.movie_id = '131' AND comments.upcomment_id IS NULL) AND "ratings"."movie_id" = 131

| So I changed and made a query in SQLite to check if it works
V
SELECT [...]
LEFT OUTER JOIN "users" ON "users"."id" = "comments"."user_id" 
LEFT OUTER JOIN "ratings" ON "ratings"."user_id" = "users"."id" AND "ratings"."movie_id" = "comments"."movie_id"
WHERE (comments.movie_id = '131' AND comments.upcomment_id IS NULL)

好像可以，但是Q2:如何在 Rails 中执行多个条件的连接(无需自己编写连接)？

由于我没有找到上述问题的答案，所以我自己编写了这些连接。我必须添加 eager_load 才能使其正常工作，并以以下查询结束:

@comments = Comment
  .where('comments.movie_id = ? AND comments.upcomment_id IS NULL', params[:movie]) 
  .joins('LEFT OUTER JOIN "comments" "replies_comments" ON "replies_comments"."upcomment_id" = "comments"."id"')
  .joins('LEFT OUTER JOIN "users" "users_replies_comments" ON "users_replies_comments"."id" = "replies_comments"."user_id"')
  .joins('LEFT OUTER JOIN "ratings" "ratings_users_replies_comments" ON "ratings_users_replies_comments"."user_id" = "users_replies_comm    ents.id" AND "ratings_users_replies_comments"."movie_id" = "comments"."movie_id"')
  .joins('LEFT OUTER JOIN "users" "users_comments" ON "users_comments"."id" = "comments"."user_id"')
  .joins('LEFT OUTER JOIN "ratings" "ratings_users_comments" ON "ratings_users_comments"."user_id" = "users_comments"."id" AND "ratings_    users_comments"."movie_id" = "comments"."movie_id"')
  .eager_load([:replies => { :user => :ratings }, :user => :ratings])

它似乎有效，但我认为它可能是性能 killer 。所以我最后的想法是加载所有带有作者/回复的评论并对其进行迭代，收集所有作者 ID 并使用“IN(id1,id2,...)”进行额外查询。然后将它作为一个单独的变量传递给 View 。像这样:

 ids = @comments.map { |c| c.user.id }
 @comments.each { |c| c.replies.map { |r| ids.push r.user.id } }
 ids.uniq!

 @ratings = Rating
   .where('movie_id = ?', params[:movie])
   .where('user_id IN(?)', ids)

或者也许我不应该简单地做任何事情并在需要时让 Rails 达到延迟加载额定值？你怎么认为？

Answer 1

所以，评分与评论不同，对吧？为什么不？使评论也充当(可选)评级并删除评级表和模型。这需要为评论添加评分并使评论正文可选。然后你只需要加载电影的所有评论。

不要担心预加载所有线程子评论的查询。当您有几百万条评论时，您会担心这一点。您应该将最顶部的“adam/eve”评论的 id 添加到每个 child 、孙子等，这样您就可以在一个查询中快速加载所有后代。您还可以添加 movie_id，这样您就可以将其放入索引并快速加载电影的每条评论，无论评论有多深。

此外，在创建或更新评分时，将电影的总评分缓存在电影表中。然后您可以显示带有评级的电影列表，而无需为每个请求执行 COUNT。