通过join和group by查询活动记录

Question

我正在设计一个API来从以下场景获取数据:

brandstable:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

items表:

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| id                        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| category_id               | bigint(20)   | YES  | MUL | NULL    |                |
| brand_id                  | bigint(20)   | YES  |     | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+

item_skus表:

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| id                        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| item_id                   | bigint(20)   | YES  | MUL | NULL    |                |
| number_of_stock           | int(11)      | YES  |     | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+

Item模型与ItemSku和Brand的关系

belongs_to :brand
has_many :skus, class_name: 'ItemSku'

简单地说，我想知道每个品牌的库存和所有商品的数量。

{
"brandCounts":[
{
"id":7006,
"name":"Brand 01",
"stockAvailableItemCount":50,
"allItemCount":60
},
{
"id":20197,
"name":"Brand 02"
"availableItemCount":150,
"allItemCount":660
}
]
}

实现:

brand_counts = []
brand_counts_hash = Hash.new()
items = Item.left_outer_joins(:skus).where(category_id: params[:id]).pluck(:brand_id, :number_of_stock, :item_id)
items.each do |item|
brand_id = item[0]
stock = item[1]
if brand_counts_hash.has_key?(brand_id)
item_count_arry = brand_counts_hash[brand_id]
stock_available_item_count = item_count_arry[0]
all_item_count = item_count_arry[1]
if stock > 0
brand_counts_hash[brand_id] = [stock_available_item_count + 1, all_item_count + 1]
else
brand_counts_hash[brand_id] = [stock_available_item_count, all_item_count + 1]
end
else
stock_available_item_count = 0
all_item_count = 0
if stock > 0
stock_available_item_count += 1
all_item_count += 1
brand_counts_hash[brand_id] = [stock_available_item_count, all_item_count]
else
all_item_count += 1
brand_counts_hash[brand_id] = [stock_available_item_count, all_item_count]
end
end
end
brand_counts_hash.each do |key, value|
stock_available_item_count = value[0]
all_item_count = value[1]
brand_counts << {
id: key,
name: get_brand_name(key),
stock_available_item_count: stock_available_item_count,
all_item_count: all_item_count
}
end
@brand_counts = brand_counts
render 'brands/counts/index', formats: :json
end
def get_brand_name(brand_id)
brand = Brand.find_by(id: brand_id)
brand.name unless brand == nil
end

有没有一种方法可以在没有多个循环的情况下进一步优化它?

Answer 1

假设您的Brand模型还定义了以下关联

has_many :items

，你想要的最终结果是

{
"brandCounts":[
{
"id":7006,
"name":"Brand 01",
"stockAvailableItemCount":50,
"allItemCount":60
},
{
"id":20197,
"name":"Brand 02"
"availableItemCount":150,
"allItemCount":660
}
]
}

当您复制并粘贴到您的项目时，以下代码可能无法工作。但是它演示了如何用更少的代码解决这个问题

Brand.includes(items: :skus).all.map do |brand|
{
id: brand.id,
name: brand.name,
stockAvailableItemCount: brand.items.count,
allItemCount: brand.items.map {|item| item.skus.sum(:number_of_stock)}.sum
}
end

如果您需要json格式，只需使用to_json对上述代码的结果。