title: Rails get count of association through join table
body: <p>This question is an offshoot from <a href="https://stackoverflow.com/questions/30130380/habtm-associations-in-rails-collecting-and-counting-the-categories-of-a-model/30132930#30132930">HABTM associations in Rails : collecting and counting the categories of a model&#39;s children</a>.</p>
  
  <h2>Given:</h2>
  
  <pre><code>class Category &lt; ActiveRecord::Base
    has_and_belongs_to_many :books
    validates_uniqueness_of :name
  end
  
  class Book &lt; ActiveRecord::Base
    has_and_belongs_to_many :categories
  end
  
  class Store &lt; ActiveRecord::Base
    has_many :books
    has_many :categories, through: :books
  end
  </code></pre>
  
  <h2>The task:</h2>
  
  <p><strong>Given a store, list the number of books per category.</strong></p>
  
  <pre><code>Store.first.books_per_category
  </code></pre>
  
  <p>desired output:</p>
  
  <pre><code>[ { name: 'mystery', count: 5 }, { name: 'fantasy', count: 6 } ]
  </code></pre>
  
  <p><strong>However each store may have an enormous amount of books and categories</strong>.</p>
  
  <p>I am trying to create a single performant query which only gets the name column and a count of the Books for each distinct Category associated with a Store, without loading books into memory.</p>
  
  <h2>I have tried so far:</h2>
  
  <pre><code>class Store &lt; ActiveRecord::Base
  
    # Will load each book into memory
    def books_per_category
      categories.eager_load(:books).map do |c|
        {
            name: c.name,
            count: c.books.size # Using size instead of count is important since count will always query the DB
        }
      end
    end
  
    # will query books count for each category.
    def books_per_category2
      categories.distinct.map do |c|
        {
            name: c.name,
            count: c.books.count
        }
      end
    end
  end
  </code></pre>
  
  <h2>Database schema:</h2>
  
  <pre><code>ActiveRecord::Schema.define(version: 20150508184514) do
  
    create_table "books", force: true do |t|
      t.string   "title"
      t.datetime "created_at"
      t.datetime "updated_at"
      t.integer  "store_id"
    end
  
    add_index "books", ["store_id"], name: "index_books_on_store_id"
  
    create_table "books_categories", id: false, force: true do |t|
      t.integer "book_id",     null: false
      t.integer "category_id", null: false
    end
  
    add_index "books_categories", ["book_id", "category_id"], name: "index_books_categories_on_book_id_and_category_id"
    add_index "books_categories", ["category_id", "book_id"], name: "index_books_categories_on_category_id_and_book_id"
  
    create_table "categories", force: true do |t|
      t.string   "name"
      t.datetime "created_at"
      t.datetime "updated_at"
    end
  
    create_table "stores", force: true do |t|
      t.string   "name"
      t.datetime "created_at"
      t.datetime "updated_at"
    end
  end
  </code></pre>