Take the 2-minute tour ×

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

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

class Company < ActiveRecord::Base
     has_one :address

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

share|improve this question
What have you tried already? Show your code attempts thus far, and you will get better response. –  xxx Feb 26 '10 at 10:48
add comment

2 Answers

up vote 1 down vote accepted

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

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.

share|improve this answer
Thanks! Your solution brought me back on track! In "class Country < ActiveRecord::Base" I now have: def to_json hash = { "Country Name" => self.name, "Amount Companies" => self.companies.size } hash.to_json end That seems to work. –  Sney Feb 26 '10 at 12:43
you should not use c.companies.size if you just want the count, since it will load all of the records (which is quite wasteful). Use c.companies.count instead. –  psyho Feb 26 '10 at 13:56
@Sney calling to_json inside method to_json should result into loop –  Sandip Ransing Feb 24 '12 at 16:03
add comment

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.

share|improve this answer
Thanks for your hints! #1: I added ":counter_cache => :companies_count" as well as the column named "companies_count" to Address, but I still have n+1 SQL queries. How do I get the counter cache to work finally? #2: By issuing the single SQL query I get all countries, even those with no company. When i append ":conditions => 'companies_count > 0'" I get no results. Is there a way to add this restriction to the query, or do I have to go through the array in Ruby? –  Sney Feb 26 '10 at 15:09
The column named companies_count should go into Country model (countries table). You also need to remember that it will set it's initial value by itself. You need something like Company.update_all('companies_count = (SELECT count(*) FROM addresses WHERE country_id = countries.id)') to set the correct initial values. After that you should be able to query Countries like you wanted to. –  psyho Feb 27 '10 at 21:34
I changed "Company.update_all..." to "Country.update_all...". Also I had to change "countries = Country.find(:all).select{|c| c.companies.count > 0 }" to "countries = Country.find(:all).select{|c| c.companies_count > 0 }". I thought rails would do some magic with the "count" method, but it seems, that I have to call the read only attribute "companies_count" explicitely. –  Sney Feb 27 '10 at 22:40
I'm guessing that Country.all(:conditions => 'companies_count > 0') should also work now, so you don't have to do it using select. And companies_count is just a cache, invoking country.companies.count will still result in a DB query. Be aware also, that while counter cache is updated on create and destroy, it is not (curiously) updated when the record is changed (Address.country_id changes for some address record), so it might get out of sync in certain situations. –  psyho Feb 27 '10 at 23:21
add comment

Your Answer


