Rails 5.2.3 - 显示按关联记录总数排序的类别树

Question

我目前正在使用 Ancestry gem 渲染 500+ 类别和子类别(它们最多可以深入 3 级(。

现在，我要做的是：

1. 仅显示具有关联的类别/子类别 交易。
2. 按以下方式对这些类别进行排序：
   • 这些关联交易记录的:amount的总和。
   • 并按层次结构对它们进行排序。
3. 打印每个类别名称旁边的总计。

以下是我希望实现的目标的示例：

Travel = $1500
Travel > Air = $1000
Travel > Ground = $250
Business = $500
Business > Services = $250
Business > Services > Marketing = $75
# etc...

这是我的模型的样子：

class Category < ApplicationRecord
has_many :transactions
has_ancestry
end
class Transaction < ApplicationRecord
belongs_to :account
belongs_to :category
end

到目前为止，我几乎能够通过以下操作到达那里：

# app/controllers/categories_controller.rb
def index
# Get all of the root categories
@primary_categories = Category.where(ancestry: nil)
end
# app/views/categories/index.html.erb
<% @primary_categories.each do |primary_category| %>
<% primary_category_total = Transaction.where(account_id: current_user, category_id: primary_category.subtree).sum(:amount) %>
<% if primary_category_total != 0.0 %> 
<%= link_to primary_category.name, category_path(primary_category) %>
<%= number_to_currency primary_category_total %>
<% if primary_category.has_children? && primary_category_total != 0.0 %> 
<% primary_category.children.each do |secondary_category| %>
<% secondary_category_total = Transaction.where(account_id: current_user, category_id: primary_category.subtree).sum(:amount) %>
<% if secondary_category_total != 0.0 %> 
<%= link_to secondary_category.name, category_path(secondary_category) %>
<%= number_to_currency secondary_category_total %>
<% if secondary_category.has_children? && secondary_category_total != 0.0 %>
<% secondary_category.children.each do |tertiary_category| %>
<% tertiary_category_total = Transaction.where(account_id: current_user, category_id: primary_category.subtree).sum(:amount) %>
<% if tertiary_category_total != 0.0 %> 
<%= link_to tertiary_category.name, category_path(tertiary_category) %>
<%= number_to_currency tertiary_category.transactions.sum(:amount) %>
# etc...

但这会产生大量的查询，而且速度非常慢，更不用说我现在有一堆复杂的代码了。当然，它们不是按总数排序的。

我应该如何处理这个问题？

Answer 1

Caveat emptor：

下面的所有内容都应被视为伪代码。我没有测试任何这些，我并没有试图让它准备好复制和粘贴。它只是帮助您开始重构的起点。

在您的模型中

你想从你的视图中得到像Transaction.where(account_id: current_user, category_id: primary_category.subtree).sum(:amount)这样的东西。

您可以创建一个方法和/或范围来返回事务总计。例如：

# app/models/transaction.rb
scope :by_user_category, ->(user, category) do 
where(account: user, category: category.subtree)
end
# app/models/category.rb
def user_transaction_amount(user)
Transaction.by_user_category(user, self).sum(:amount)
end

在控制器中

你应该急于加载孩子。你知道你会需要它们，所以一次做完。(在原因范围内，见下文(

你提到500+类别，这是分页的吗？如果是这样，请分批进行。

在您的视野中

注意到重复代码了吗？尝试使用部分来干燥它。例如：

# app/views/categories/_category.html.erb
<% @categories.each do |category| %>
<% category_total = category.user_transaction_amount(current_user) %>
<% return if category_total == 0.0 %>
<%= link_to category.name, category_path(category) %>
<%= number_to_currency category_total %>
<% if category.has_children? && category_total != 0.0 %> 
<% category.children.each do |secondary_category| %>
<%= render category, category: secondary_category %>
<% end %>
<% end %>
<% end %>

# app/views/categories/index.html.erb
<% @primary_categories.each do |primary_category| %>
<% render 'categories', category: primary_category %>
<% end %>

这仍然可以进行一些改进。视图模板应该主要是 html 语句，这是 100% 红宝石。您可以将所有这些移动到帮助程序中，甚至可以直接从控制器操作进行渲染。

Answer 2

经过一番尝试(基于 Jacob 的第一个答案(，我想出了一个性能明显更好的解决方案，消除了控制器和视图的复杂性，并完成了我第一篇文章中需求列表中的所有操作。

我仍然认为有优化和清理的空间，但它在这里：

app/models/transaction.rb

scope :by_user_category, ->(user, category) do 
where(account: user.accounts, category: category.subtree)
end

应用/模型/类别.rb

def balance(user)
Transaction.by_user_category(user, self).sum(:amount)
end
def self.spending_by(user)
categories_with_spending = []
categories_depth_0 = Category.where(ancestry: nil) # Get the root categories
categories_depth_0.each do |cat_depth_0|
category_depth_0_balance = cat_depth_0.balance(user)
if category_depth_0_balance < 0  # "Root category exists and has a balance"
categories_depth_1_with_spending = []
categories_depth_1 = Category.find_by_id(cat_depth_0).children # Get the sub-categories
if !categories_depth_1.empty? # "Sub-category exists"
categories_depth_1.each do |cat_depth_1|
category_depth_1_balance = cat_depth_1.balance(user)
if category_depth_1_balance < 0 # "Sub-category exists and has a balance"
categories_depth_2_with_spending = []
categories_depth_2 = Category.find_by_id(cat_depth_1).children
if !categories_depth_2.empty? # Sub-category has child
categories_depth_2.each do |cat_depth_2|
category_depth_2_balance = cat_depth_2.balance(user)
if category_depth_2_balance < 0  # Sub-category child has a balance
categories_depth_2_with_spending << {
category: cat_depth_2,
balance: category_depth_2_balance
}
end
end
end
if categories_depth_2_with_spending != nil
# Passing child sub-categories to parent sub-categories
categories_depth_1_with_spending << {
category: cat_depth_1,
balance: category_depth_1_balance,
sub_categories: categories_depth_2_with_spending.sort_by { |c| c[:balance] }
}
end
end
end
if categories_depth_1_with_spending != nil
# Passing sub-categories to root category
categories_with_spending << {
category: cat_depth_0,
balance: category_depth_0_balance,
sub_categories: categories_depth_1_with_spending.sort_by { |c| c[:balance] }
}
end
else
# "Root exists but has no sub-categories"
categories_with_spending << {
category: cat_depth_0,
balance: category_depth_0_balance
}
end
end
end
return categories_with_spending.sort_by { |c| c[:balance] }
end

app/controllers/categories_controller.rb

def index
@categories = Category.spending_by(current_user)
end

app/views/categories/index.html.erb

<% @categories.each do |cat_depth_0| %>
<section class="app-card app-amount-summary app-categories__card">
<%= render 'category_depth_0', category: cat_depth_0 %>
<% if cat_depth_0[:sub_categories] %>
<ul class="app-category-list">
<% cat_depth_0[:sub_categories].each do |cat_depth_1| %>
<%= render 'category_depth_1', category: cat_depth_1 %>
<% end %>
</ul>
<% end %>
</section>
<% end %>

app/views/categories/_category_depth_0.html.erb

<header class="app-card-header">
<h3 class="app-card-header__h3">
<%= link_to category[:category].name, category_path(category[:category].id) %>
</h3>
<p class="app-card-header__balance"><%= number_to_currency category[:balance] %></p>
</header>

_category_depth_1.html.erb的工作方式与_category_depth_0.html.erb完全相同，但结构不同，因此我在此示例中跳过了它。