ActiveRecord group by on a join

Question

Really been struggling trying to get a group by to work when I have to join to another table. I can get the group by to work when I don't join, but when I want to group by a column on the other table I start having problems.

Tables:
Book
id, category_id

Category
id, name

ActiveRecord schema:

class Category < ActiveRecord::Base
  has_many :books
end

class Book < ActiveRecord::Base
  belongs_to :category
end

I am trying to get a group by on a count of categories. I.E. I want to know how many books are in each category.

I have tried numerous things, here is the latest,

books = Book.joins(:category).where(:select => 'count(books.id), Category.name', :group => 'Category.name')

I am looking to get something back like

[{:name => fiction, :count => 12}, {:name => non-fiction, :count => 4}]

Any ideas?

Thanks in advance!

Answer 1

How about this: Category.joins(:books).group("categories.id").count It should return an array of key/value pairs, where the key represents the category id, and the value represents the count of books associated with that category.