Query with Ruby on Rails

Question

Hi: I'm struggling with a rails query. Finally, I want to make a JSON response with the following contents: A listing of all countries which have at least one company, associated through "Address" and a count of how of how many companies reside in a country. Here is my simple model structure:

class Country < ActiveRecord::Base
     has_many :addresses
end

class Address < ActiveRecord::Base
     belongs_to :country
     belongs_to :company
end

class Company < ActiveRecord::Base
     has_one :address
end

What's the most elegant way to solve this with Rails?

Answer 1

You need to overload the to_json method :

class Country < ActiveRecord::Base
     has_many :addresses
     def to_json
         # format your country as json with all the elements you need
     end
end

Then in your controller do something like that:

countries = Country.find(:all).select{|c| c.companies.size > 0 }
json = countries.collect{|c| c.to_json }

You'll have to use has_many throught to get the companies from a country. Documentation here: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Sorry I don't have time for a more precise solution, but hopefully these pointers will help you. If you have specific problems, feel free to either comment or open a new question.

Answer 2

You might want to add a counter cache to Address

belongs_to :country, :counter_cache => :companies_count

It stores the number of companies in the country model, which saves you from the N+1 query issue.

Alternatively you could avoid it also by issuing a single query like this:

Country.find(:all, :select => 'countries.*, count(addresses.id) as companies_count', :joins => 'LEFT JOIN addresses ON addresses.country_id = countries.id', :group => 'countries.id')

This way all of the returned countries will have field companies_count containing the number of companies for that country.