ruby-on-rails - Rails 通过连接表获取关联计数

Question

这个问题是 HABTM associations in Rails : collecting and counting the categories of a model's children 的一个分支.

给出:

class Category < ActiveRecord::Base
  has_and_belongs_to_many :books
  validates_uniqueness_of :name
end

class Book < ActiveRecord::Base
  has_and_belongs_to_many :categories
end

class Store < ActiveRecord::Base
  has_many :books
  has_many :categories, through: :books
end

任务:

给定一家商店，列出每个类别的图书数量。

Store.first.books_per_category

期望的输出:

[ { name: 'mystery', count: 5 }, { name: 'fantasy', count: 6 } ]

然而，每个商店可能有大量的书籍和类别。

我正在尝试创建一个单一的高性能查询，它只获取名称列和与商店关联的每个不同类别的书籍计数，而不将书籍加载到内存中。

到目前为止我已经尝试过:

class Store < 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

数据库模式:

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

Answer 1

您需要在 Categories 对象上创建一个方法(或作用域)，例如。

Category.joins(:books)
        .select('categories.*, COUNT(books.id) as book_count')
        .group('categories.id')

结果对象现在将具有类别实例的每个属性，并响应方法 book_count，该方法返回具有该实例类别 ID 的书籍数量。

值得注意的是，这将忽略任何与书籍无关的类别。如果您想包括这些，则需要将查询更新为以下内容:

Category.left_outer_joins(:books)
        .select('categories.*, COUNT(books_categories.book_id) as book_count')
        .group('categories.id')