##Letting Your ActiveRecord Associations Help
Suppose you have the following models:
class User < ActiveRecord::Base
has_many :blogs
has_many :posts, through: :blogs
has_many :received_comments, through: :posts, source: :comments
has_many :expressed_comments, class_name: 'Comment', foreign_key: 'user_id'
end
# Note:
# received comments is comments from any user on posts written by this user
# expressed comments is comments this user has made on any user's posts
class Blog < ActiveRecord::Base
belongs_to :author, class_name: 'User', foreign_key: 'user_id'
has_many :posts
has_many :comments, through: :posts
end
class Post < ActiveRecord::Base
belongs_to :blog
delegate :author, to: :blog, prefix: false, allow_nil: true
end
class Comment < ActiveRecord::Base
belongs_to :user
belongs_to :post
end
Examples:
Posts ordered by the number of comments on them:
Post.joins(:comments).group(:id).order('count(comments.id) desc')
Posts that have no comments on them:
Post.includes(:comments).where(comments: {id:nil})
Using the has_many through association you can do the same for Blog:
Blog.includes(:comments).where(comments: {id:nil})
Suppose comments have a like / dislike attribute stored as +1 / -1 in a like_value field. Posts ordered by most liked is then:
Post.joins(:comments).group(:id).order('sum(comments.like_value) desc')
Getting the net like value for a particular post:
p = Post.last
p.comments.sum(:like_value)`
Or the overall like value for a whole blog:
Blog.last.comments.sum(:like_value)
Or an author:
User.last.received_comments.sum(:like_value)
Total number of comments an author got:
User.last.received_comments.count
Authors ranked by popularity (highest net likes first):
User.joins(:received_comments).group(:id).order('sum(comments.like_value) desc')
Authors with at least five likes regardless of dislikes:
User.joins(:received_comments).where('comments.like_value = 1').group(:id).having('count(comments.id) >= 5')
or equivalently
User.joins(:received_comments).where(comments: {like_value: 1}).group(:id).having('count(comments.id) >= 5')
Authors whose net like balance is at least 3:
User.joins(:received_comments).group(:id).having('sum(comments.like_value) >= 3')
Users who have commented on blog 3:
User.where(id: Comment.joins(:post).where(posts: {blog_id: 3}).pluck(:user_id))