Reputation: 516
My apologies if this already exists, I've looked but can't find this exact question.
Say I have three ActiveRecord models, Continent
, Country
, and Town
. Town
has an attribute population
, and I'm interested in breaking down the population in each country, grouped by continent, by summing the populations of the various towns.
Because there are many towns in a country, but relatively few countries in a continent, and only several continents, I might proceed doing something like this:
continents = {}
Continent.each do |continent|
country_breakdown = {}
continent.countries.each do |country|
country_breakdown[country] = country.towns.sum(:population)
end
continents[continent] = country_breakdown
end
... followed by iterating through continents
in a view.
The problem is that this has SQL complexity O(n)
, where n
is the number of countries.
My question is this: is there some way to get SQL to produce (in a single query) this grouped result, which we could then iterate through just like a normal nested Hash
on the view side in Rails?
I've tried working with ActiveRecord::QueryMethods#group
, but I'm utterly confused about how to iterate through what results from that.
Upvotes: 2
Views: 3621
Reputation: 4813
Yes, there is. Assuming you have your relations fully described:
class Continent
has_many :countries
has_many :towns, through: :countries
end
class Country
belongs_to :continent
has_many :towns
end
class Town
belongs_to :country
has_one :continent, through: :country
end
You should be able to run:
results = Continent.joins(:town).group(:continent_id, :country_id).sum(:population)
Which will give you a hash in the format [[continent_id, country_id], population]
.
e.g. {[2, 38]=>39993, [1, 31]=>127425, [5, 12]=>113556, [5, 76]=>2966, [1, 10]=>263473, [1, 34]=>154492, [2, 37]=>55087...}
The join gives you access to the Town
model in your query, which Continent
knows about because of the has_many through:
relation.
You can than iterate on that hash to get the data in the format you'd like:
formatted = results.each_with_object({}) do |((continent_id, country_id), pop), m|
m[continent_id] ||= {}
m[continent_id][country_id] = pop
end
e.g. { continent_id => { country_id => population } }
{2=>{38=>39993, 37=>55087}, 1=>{31=>127425, 10=>263473...}
There's likely more efficient ways to perform this step. It may be useful to map each entry to a real object so you don't get lost about which number refers to which value. But in any case, this reduces your database access down to one query which will be much faster the original implementation.
Upvotes: 2