I have a has_and_belongs_to_many relationship setup. It looks like this:
- books
have_and_belong_to_many categories
- categories
have_and_belongs_to_many books
- a store
has_many books
- a book
belongs_to a store
I'm trying to show how many books in each store belong to each category. So my view would show Store X has 200 books and 80 of them are mystery, 60 are non fiction, etc.
I have been trying out a bunch of different ways of doing this, but no success so far. I think I'm starting in the wrong place. Any direction would be much appreciated.
Thanks
This is Rails 4 and psql by the way.
Provided that you have a books_categories join table you can add a has_many :categories, through: :books association to which links stores and categories through books.
class Store < ActiveRecord::Base
has_many :books
has_many :categories, through: :books
end
That's the easy part. Now lets get each category and the books count (revised):
def books_per_category
categories.select('categories.id, categories.name, count(books.id) as count').group('categories.id, categories.name').map do |c|
{
name: c.name,
count: c.count
}
end
end
Courtesy of @jakub-kosiński
Generally, instead of using Rails' built-in 'has_and_belongs_to_many' method, it is better practice to use a join table. In this setup, you have three tables:
- Books
- Categories
- BookCategories
The BookCategories (or whatever you decide to call it) is a join table that belongs_to both Books and Categories and has a Foreign ID of each. You would then use Rails' "has_many :through" to link the Books and Categories.
The store would have a 'has_many' relationship with books. With the prior relationship setup right, you can then use this method to get the count for a store for a particular category:
Store.books.where(category:'Mystery')
