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)
精彩评论