How to get build Rails ActiveRecord query to group objects that belong_to other model?

Question

Given the following models:

ServiceCenter
  has_many :country_codes

CountryCode
  belongs_to :service_center

What is the ActiveRecord query that will return something like this:

{
  "US": ["United States"],
  "PT": ["Portugal", "Spain", "Estonia"],
  "FR": ["France", "Germany", "Austria"]
}

where the keys are the country attribute of each ServiceCenter and the values are the country_name attributes of each CountryCode that belongs to it? In other words, I want just a list of which CountryCodes belong to each ServiceCenter, showing only those attributes.

{ 'service_centers.country': 'country_code.country_name', 'country_code.country_name' }

I have tried this:

CountryCode .joins(:service_center) .select('country_codes.country_name', 'service_centers.country') .group('service_centers.country')

but this returns:

<ActiveRecord::Relation [
  <CountryCode id: nil, country_name: "Portugal">,
  <CountryCode id: nil, country_name: "United States">,
  <CountryCode id: nil, country_name: "Portugal">.....]>

I also tried ServiceCenter.joins(:country_code).... but similar result - an ActiveRecord Relation with ServiceCenter objects whose ids were nil, and whose country attributes were given.

I've looked at answers similar to this one: Get all records grouped by field from association and sorted by count in group, but I don't want a count.

I would appreciate any help with this!!

Answer 1

It's not recommended to get all records like below, as it'll have optimisation issue. But, for you understanding, you may try:

hash = {}

ServiceCenter.all.each do |service_center|
  hash[service_center.country] = service_center.country_codes.pluck(:country_name)
end

the output hash would be like, eg:

{
  "US": ["United States"],
  "PT": ["Portugal", "Spain", "Estonia"],
  "FR": ["France", "Germany", "Austria"]
}

Note: Hash can't have multiple values as you have specified, it should be in the form of Array.

EDIT

Not exactly what you want, but this may help a bit:

ServiceCenter.joins(:country_codes).group("service_center_id").pluck("service_centers.country, GROUP_CONCAT(country_codes.country_name)")

Output

[["US", "United States"], ["PT", "Portugal, Spain, Estonia"], ["FR", "France, Germany, Austria"]]