Selecting users with biopsies in a given country for charting

I have this monstrosity

@nation_biopsy =  
       User.all.where(country: @nation).group_by{ |u| [u.institute_type, u.biopsy] }
      .map{ |i, o| ["name" => i[0], "y" => User.where(id: o.map(&:id))
      .sum("biopsy")]}.flatten.to_json

What it is doing is finding all the users in my database that belong to a country (@nation) and grouping them according to their "institute_type" and then "biopsy" (both columns for users). Then it is trying to convert it to an array of JSON objects for a "Highcharts" script. It works, but as a newbie I am sure this is very database heavy. Is there a better way?

Some notes on your code:

• User.all.where -> User.where.

• The moment you write group_by you are not using SQL anymore, performance will suffer.

• The line starting with sum is very confusing, the indentation make you think it's the continuation of map but in fact it's part of its inner expression. Indentation should reflect the structure of an expression.

• map + flatten -> flat_map.

• |pair, something| and then pair[0]. You can de-structure arrays using the syntax |(k, v), something|.

On a first refactor, I'd write:

@nation_biopsy = User
  .where(country: @nation)
  .group_by { |u| [u.institute_type, u.biopsy] }
  .flat_map do |(institute_type, biopsy), users| 
    ["name" => institute_type, "y" => User.where(id: users.map(&:id)).sum("biopsy")]
  end.to_json

On a second refactor, I'd try to make it work with SQL. Something like this:

@nation_biopsy = User
  .where(country: @nation)
  .group(:institute_type, :biopsy)
  .select(:institute_type, "SUM(biopsy) AS biopsy_count")
  .flat_map { |group| ["name" => group.institute_type, "y" => group.biopsy_count] }
  .to_json

Now, if you want to write something fancy use Arel, it allows you to write it without SQL fragments, which looks kind of cool:

users = User.arel_table
@nation_biopsy = User
  .where(users[:country].eq(@nation))
  .group(users[:institute_type], users[:biopsy])
  .select(users[:institute_type], users[:biopsy].sum.as("biopsy_count"))
  .flat_map { ["name" => group.institute_type, "y" => group.biopsy_count] }
  .to_json

StackExchange Code Review Q#113738, answer score: 4