Can I get the same output of the SQL query below using ActiveRecord?
SELECT d.title, COUNT(ep.*)
FROM posts d
LEFT JOIN comments ep
ON d.title = ep.post_title
GROUP BY d.title;
I tried Post.joins(:comments).group(:title).count('comments.*').
Your relationship, or at least this request, is very unconventional.
Typically comments would be related to posts through the addition of a post_id to the comments table.
That being said if this is not the case you could add the relationship using the title as follows:
class Post < ApplicationRecord
has_many :comments, foreign_key: 'post_title', primary_key: 'title', inverse_of: :post
end
class Comment < ApplicationRecord
belongs_to :post, foreign_key: 'post_title', primary_key: 'title', inverse_of: :comments
end
Then you can use Post.left_joins(:comments).group(:title).count('comments.*')
If you already have a natural relationship set up and for some reason are trying to create this as a one off associated query you can do so as follows.
posts_table = Post.arel_table
comments_table = Comment.arel_table
join = Arel::Nodes::OuterJoin.new(
comments_table,
Arel::Nodes::On.new(posts_table[:title].eq(comments_table[:post_title]))
)
Post.joins(join).group(:title).count('comments.*')
Both will produce the desired SQL (sans the aliasing) and will return a Hash in the form of {"post_title_1" => count_of_comments, "post_title_2" => count_of_comments, ...}