Using the group function to return an array of ID's as a value and the .group(:field) as the key

Question

I'm trying to group a large table by a foreign key in order to make some changes to those records.

What I would like to do is group(:foreign_key_id) and return the ids in this group. So I would get: {foreign_key_id => [array of ID's with this key], {foreign_key_id => [array of ID's with this key] }

Maybe I should be mapping the (&:id) value, but this function sits behind a where so it's not all records of this foreign key that are returned.

My working code would be something like this:

SubscriptionTag.where(subscription_id: @paper.subscriptions.map(&:id).group(:tag_id)

This returns all unique tags used by my subset of subscriptions. Now I would like to also return the SubscriptionTag ids along with each tag_id

I hope this makes sense.

There are many ways to do this. What is your RDBMS ? – Pavel Mikhailyuk Mar 21 '18 at 11:57 — Pavel Mikhailyuk, Mar 21 '18 at 11:57
Hey @PavelMikhailyuk. I'm using Postgres – David Sigley Mar 21 '18 at 12:02 — David Sigley, Mar 21 '18 at 12:02

score 1 · Accepted Answer · answered Mar 21 '18 at 12:06

1

Based on this answer, you can use pluck with array_agg and then #to_h:

Subscription.group(:tag_id).pluck(:tag_id, 'array_agg(id)').to_h

answered Mar 21 '18 at 12:06

Pavel Mikhailyuk

2,757
9
17

Excellent! Thanks for your help – David Sigley Mar 21 '18 at 13:17

Using the group function to return an array of ID's as a value and the .group(:field) as the key

1 Answers1