比较来自世界各地的卖家的域名和 IT 服务价格

Rails 通过表格获取关联数量 join

这个问题是一个分支
https://coderoad.ru/30130380/
.

达娜:


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


但是,每个商店都可能有大量的书籍和类别。

.

我正在尝试创建一个只接收列的富有成效的请求 name 以及与商店相关的每个单独类别的书籍数量,而不在内存中加载书籍。

到目前为止,我曾试过:


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
已邀请:

莫问

赞同来自:

你可以使用链条
select


group

汇总每个类别的书籍数量。 你的方法
books_per_category

可能是这样的:


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:


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

八刀丁二

赞同来自:

你想创建一种方法吗? /或范围/ 对于对象 Categories, 所以有些东西。


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


生成的对象现在将具有每个实例属性 category 并回应该方法
book_count

, 返回本识别类别的本标识符的书籍数量。
值得注意的是要注意,这将允许您省略书籍无关的任何类别。 如果要启用它们,则查询必须更新到下一个:


Category.joins/'LEFT OUTER JOIN books_categories on books_categories.category_id = categories.id'/.select/'categories.*, COUNT/books_categories.book_id/ as book_count'/.group/'categories.id'/

要回复问题请先登录注册