ActiveRecord group not returning hash with arrays of objects for values

Question

I know this has been asked and answered a lot. I have two tables Foo and Bar.

class Foo < ApplicationRecord
  belongs_to :bar
  ...

Foo has attributes of id and name and bar_id

and

class Bar < ApplicationRecord
  has_many :foos
  ...

Bar has the attributes, id and name.

When I simply try Foo.group(:bar_id) I get #<Foo::ActiveRecord_Relation:0x3fdeac1cc274>

With Foo.group(:bar_id).count I get {5=>2, 1=>2} the keys being the bar_id and the values the count of how many have that id.

What I'm trying to do is, group Foo on Bar#name with an array of Foos as the values.

{
 'name1' => [#<Foo:0x00007fbd5894f698 id:1, name: 'thing'...}, ...],
 'name2' => [#<Foo:0x00017fbd5894f698 id:5, name: 'thing'...}, ...],
 ...
}

With Foo.joins(:bar).group('bars.name').count I am able to return {"name1"=>2, "name2"=>2} But not an array of the Foo models. I know it's because of the count. But without the count it simply returns #<Foo::ActiveRecord_Relation:0x3fdeac1cc274>

I see a lot of suggestions using Enumerable#group_by. I don't want to use an enumerable as I'm using ActiveRecord and as the records increase, it will drastically slow down the look up.

ActiveRecord_Relation is a set of records. You need to invoke to_a on it, or iterate over it. — user229044
– user229044 ♦, Commented Jul 18, 2018 at 13:57
@meagar I get this error when iterating or adding to_a : ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "foos.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "foos".* FROM "foos" INNER JOIN "bars" ON "... ^ : SELECT "foos".* FROM "foos" INNER JOIN "families" ON "bars"."id" = "fooss"."bar_id" GROUP BY bars.name from /2.5.0/lib/ruby/gems/2.5.0/gems/activerecord-5.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:603:in async_exec'` — Int'l Man Of Coding Mystery
– Int'l Man Of Coding Mystery, Commented Jul 18, 2018 at 14:06

Igor Drozdov · Accepted Answer · 2018-07-18 14:23:54Z

1

I've noticed that you're using PostgreSQL. Why not then use the json aggregation functions. It a bit differs from your desired result, but still contains the same information:

Bar
  .joins(:foos)
  .group("bars.name")
  .pluck("bars.name", "json_agg(json_build_object('id', foos.id, 'name', foos.name))")
  .to_h

The result is going to be:

{
 'name1' => [{id:1, name: 'thing'}, ...],
 'name2' => [{id:5, name: 'thing'}, ...],
 ...
}

answered Jul 18, 2018 at 14:23

Igor Drozdov

15.1k5 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Int'l Man Of Coding Mystery Over a year ago

I've only used json_agg once and am unfamiliar with json_build_object. guess I've got some reading to do. It's close though. That gives me something to work with. However, that throws all kinds of warnings about raw SQL and will be disallowed in Rails 6

Int'l Man Of Coding Mystery Over a year ago

Since this question, I've learned about using Arel.sql to get the raw sql warning to go away :D

Collectives™ on Stack Overflow

ActiveRecord group not returning hash with arrays of objects for values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related