Rails Query group and pluck to Return Array of IDs grouped by another attribute?

Question

I know that Model.group(:account_id).count will return a hash of account_ids => counts.

{3=>3, 4=>3, 8=>8, 10=>5, 20=>4}

I'd like to return a hash of account_ids => ids

{
  3=>[4594, 4599, 4595], 
  8=>[4600, 4603, 4604, 4606, 4609, 4613, 4611, 4605], 
  20=>[4621, 4623, 4624, 4620], 
  10=>[4626, 4627, 4630, 4631, 4628], 
  4=>[222, 1189, 715]
}

I was dreaming that Model.group(:account_id).pluck(:id) would do the trick, but it throws an error:

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column 
"models.id" must appear in the GROUP BY clause or be used in an 
aggregate function
LINE 1: SELECT "models"."id" FROM "models...

My actual solution is rather ugly. It seems like there should be an easier way

Model.pluck(:id, :account_id).to_h
  .group_by{|k,v| v}
  .transform_values {|v| v.map(&:first) }

Answer 1

At least in newer versions of Rails and PostgreSQL this is possible:

pairs = Model.select(:account_id, 'array_agg(id)')
             .group(:account_id)
             .pluck(:account_id, 'array_agg(id)')

This returns:

[
  [1, [535, 536]],
  [2, [542, 567, 588]],
]

which can be converted into a hash:

Hash[*pairs.flatten(1)]

{
  1 => [535, 536],
  2 => [542, 567, 588],
}

array_agg is a PostgreSQL function, but I assume MySQL's GROUP_CONCAT would work, too.

Answer 2

I think the question is not relevant anymore but it would help someone who came across similar question.

query = "SELECT account_id, GROUP_CONCAT(id) FROM model_name GROUP BY account_id"
Model.connection.execute(query).to_h

=> {1=>"31", 22=>"12,11,10", 78=>"36,35,34,37,1,3,2,38"} # example result

The function GROUP_CONCAT() works for MySQL 5.6. For MySQL 5.7+ you may use JSON_ARRAYAGG().
For PostgreSQL you need to use array_agg() or string_agg()

Hope it will help.