Rails: query children and grandchildren, run method on child, SQL SUM on grandchildren -- resulting in array?

Question

Models:

class Category < ApplicationRecord
  has_many :inventories
  has_many :stocks, through: :inventories
end

class Inventory < ApplicationRecord
  belongs_to :category
  has_many :stocks
end

class Stock < ApplicationRecord
  belongs_to :inventory
end

Goal:

Achieve an efficient ActiveRecord query that builds an array like this:

[
   { name: "Supplies", count: 10.00, total_value: 40.00 },
   { name: "Materials", count: 25.00, total_value: 30.00 }
]

name -> just a regular attribute in Inventory model

count -> a SQL SUM on the :count column in stocks table

total_value -> from a method in the Inventory model that does some math

This could be a total fantasy but I have a large dataset so am trying to make this hyper efficient. Any ideas?

---

Edit to answer question:

total_value is a method on Inventory that then calls a sum of a method on Stock:

def total_value
  stocks.map do |stock|
    stock.total_cost
  end.sum
end

total_cost is a method on Stock:

def total_cost
  cost_per_unit * count
end

Answer 1

Here you go: query = Inventory.group(:id, :name).select(:id, :name).left_joins(:stocks).select("SUM(stocks.count) AS count").select("SUM(stocks.cost_per_unit * stocks.count) AS total_value")

query.as_json gives what you're looking for.

You can also access the data via find_each: query.find_each { |record| puts "record #{record.name} has a total value of #{record.total_value}" }

If you want to avoid duplicating the logic of total_value in SQL, you have to load stocks records, which considerably slows down the computation if there are many:

Upgrade the model

class Inventory < ApplicationRecord
  def stocks_count
    stocks.sum(&:count)
  end

  def total_value
    stocks.sum(&:total_cost)
  end
end

And the query

Inventory.preload(:stocks).map do |inventory|
  {
    name: inventory.name, 
    count: inventory.stocks_count, 
    total_value: inventory.total_value
  }
end

If you want to optimize your query to the max, you may consider caching 2 columns total_value and stocks_count on the inventories table. You would update them everytime one of its stocks changes (creation, deletion, update). It's harder to maintain but that's the fastest option.