StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I find all cities with childs?
primarykey
Id
7350853
data
AcceptedAnswerId
7351193
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-09-08T15:46:35.187
FavoriteCount
0
LastActivityDate
2011-09-08T18:24:19.473
LastEditDate
2011-09-08T18:24:19.473
LastEditorUserId
929062
OwnerUserId
929062
ParentId
0
PostTypeId
1
Score
0
ViewCount
113
LastEditorDisplayName
text
Body
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 (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: <pre><code>@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" </code></pre> 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 <a href="http://m.onkey.org/active-record-query-interface" rel="nofollow">http://m.onkey.org/active-record-query-interface</a> 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: <pre><code>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 </code></pre> 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.
Tags
<ruby-on-rails><ruby><ruby-on-rails-3>
Title
How can I find all cities with childs?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USuser929062
UserOwnerUserId
1. USuser929062
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.