CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
dfaulken
dfaulken

Reputation: 516

Rails, iterate through result of group query

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

Answers (1)

chrismanderson
chrismanderson

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

Related Questions