I'm currently using the Ancestry gem to render 500+ categories and sub-categories (they can go up to 3 levels deep).
Now, what I'm trying to do is:
:amount of those associated transactions.Here's an example of what I'm hoping to achieve:
Travel = $1500
Travel > Air = $1000
Travel > Ground = $250
Business = $500
Business > Services = $250
Business > Services > Marketing = $75
# etc...
This is what my models look like:
class Category < ApplicationRecord
has_many :transactions
has_ancestry
end
class Transaction < ApplicationRecord
belongs_to :account
belongs_to :category
end
So far I was able to ALMOST get there by doing:
# 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...
But this generates an insane amount of queries which it's painfully slow, not to mention that I now have a bunch of complex code on my view. And of course, they are not sorted by total.
How else should I approach this?
After a bit of trial an error (based on Jacob's first answer) I came up with a solution that performs significantly better, removed complexity from the controller and views and does everything on my requirements list from my first post.
I still think there is room for optimization and clean up but here it goes:
app/models/transaction.rb
scope :by_user_category, ->(user, category) do
where(account: user.accounts, category: category.subtree)
end
app/models/category.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 works exactly like _category_depth_0.html.erb but with a different structure so I skipped it on this example.