ruby-on-railsactiverecordarel

Active Record counting group from association


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.*').


Solution

  • 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, ...}