How can I find all cities with childs?

I have a City model and a Business model. Business belogs_to :city

Now I want to define a view with a list of only those cities who have a child 开发者_JS百科(in this case a business). Empty cities (means cities which still have no business added) should not be considered. If possible the list should be sorted in a way that the city with most businesses is on the top.

I found a solution like this:

@cities = City.find :all, 
       :joins => "INNER JOIN businesses ON businesses.city_id = cities.id", 
       :select => "cities.*, count(businesses.id) businesses_count", 
       :group => "businesses.city_id HAVING businesses_count > 0",
       :order => "businesses_count desc"

This works fine (sorting is not yet done), but as far as I understood this will not work with Rails 3.1 and 3.2 (I use 3.0 now). See http://m.onkey.org/active-record-query-interface

Can anybody let me know how to define my @cities in a way that is ok for Rails 3.1 and 3.2?

Thank you!

@KandadaBoggu:

Great, I very much like your answer 2), thanks!!

Just a comment:

I migrated with rails generate migration add_businesses_count_to_cities businesses_count:integer

Then I needed to edit the migration:

class AddBusinessesCountToCities < ActiveRecord::Migration
  def self.up
    add_column :cities, :businesses_count, :integer, :default => 0

    City.reset_column_information

    City.all.each do |c|
      c.update_attribute :businesses_count, c.businesses.count
    end
  end

  def self.down
    remove_column :cities, :businesses_count
  end
end

This is important to set a default value of 0 and then update the cities with the current number of businesses.

I also added the counter_cache to the child (business) like: belongs_to :city, :counter_cache => true

This way it works great.

---

1) Without sorting:

City.joins(:businesses).select("DISTINCT cities.*")

2) Using counter_cache

Add an integer column called business_count to cities table.

class City < ActiveRecord::Base
  has_many :businesses, :counter_cache => :business_count
end

Now you can select the cities as follows:

City.where("business_count > 0").order(:business_count)

3) Using group by

City.joins("
   ( SELECT a.id, COUNT(*) as business_count
     FROM   cities a, businesses b
     WHERE  a.id = b.city_id
     GROUP BY a.id 
   ) c ON cities.id = c.id ").
 select("cities.*, c.business_count AS business_count").
 order(:business_count)