Rails通过连接表获取关联计数 [英] Rails get count of association through join table

问题描述

此问题来自 Rails中的HABTM关联：收集并计算模型子项的类别。

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 } ]

不过，每家商店可能都有大量的书籍和类别。

我正在尝试创建一个性能查询，该查询仅获取与商店相关联的每个不同类别的名称列和书籍数，而无需加载书到内存中。

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.

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

数据库架构：

Database schema:

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

推荐答案

您可以使用链 select 和 group 汇总每个类别的图书数量。您的 books_per_category 方法可能看起来像这样：

You can use chain select and group to aggregate the count of books for each category. Your books_per_category method may look like this:

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

这将产生以下SQL查询：

This will produce the following SQL query:

SELECT categories.id, categories.name, count(books.id) as count 
  FROM "categories" 
  INNER JOIN "books_categories" ON "categories"."id" = "books_categories"."category_id" 
  INNER JOIN "books" ON "books_categories"."book_id" = "books"."id" 
  WHERE "books"."store_id" = 1 
  GROUP BY categories.id, categories.name

这篇关于Rails通过连接表获取关联计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！