Hi folks. I''m trying to order a find result by a count of the child models. Example: Farmer has many chickens. Chickens belong to farmer. I want farmers returned, ordered by how many chickens they have. How can I do this? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 26 Nov 2007, at 11:23, Douglas Shearer wrote:> > Hi folks. > > I''m trying to order a find result by a count of the child models. > > Example: > > Farmer has many chickens. > Chickens belong to farmer. > > I want farmers returned, ordered by how many chickens they have.If you use a counter cache then this happens for free, if not something like Farmer.find_by_sql <<_SQL select farmers.*, count(*) as chicken_count from farmers left outer join chichens on farmer_id = farmers.id group by farmers.id order by chicken_count desc SQL would probably do the job Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
You can do something like this....
#In the model...
class Farmer < ActiveRecord::Base
has_many :chicken
def self.getFarmers_on_chicken_count()
find (:all, :joins => "INNER JOIN chickens on chickens.farmer_id
= farmers.id", :select => "farmers.*, count(chickens.id)
chickens_count", :group => "chickens.farmer_id HAVING
chickens_count <
10")
end
end
# In the controller
@all_needed_farmers = Farmer.getFarmers_on_chicken_count()
Here, the output will be all the farmers with chickens less than 10.
If you want to pass the parameter "count_limit" instead of 10...
you can override the function as:
@all_needed_farmers = Farmer.getFarmers_on_chicken_count(count_limit)
Regards.
On Nov 26, 4:23 pm, Douglas Shearer
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> Hi folks.
>
> I''m trying to order a find result by a count of the child models.
>
> Example:
>
> Farmer has many chickens.
> Chickens belong to farmer.
>
> I want farmers returned, ordered by how many chickens they have.
>
> How can I do this?
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Hi again. Kiran Soumya wrote:> You can do something like this.... > > #In the model... > class Farmer < ActiveRecord::Base > has_many :chicken > > def self.getFarmers_on_chicken_count() > find (:all, :joins => "INNER JOIN chickens on chickens.farmer_id > = farmers.id", :select => "farmers.*, count(chickens.id) > chickens_count", :group => "chickens.farmer_id HAVING chickens_count < > 10") > end > > endThanks for the replies. This was along the lines of what I was looking for, but I forgot the most important thing... Say chickens have a color, simply a string, such as ''red''. How would I order the farmers by the number of red chickens they have? Sorry to be such a pain, I should have remembered this the first time round. Thanks again. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 26 Nov 2007, at 22:50, Douglas Shearer wrote:> > Hi again. > > Kiran Soumya wrote: >> You can do something like this.... >> >> #In the model... >> class Farmer < ActiveRecord::Base >> has_many :chicken >> >> def self.getFarmers_on_chicken_count() >> find (:all, :joins => "INNER JOIN chickens on chickens.farmer_id >> = farmers.id", :select => "farmers.*, count(chickens.id) >> chickens_count", :group => "chickens.farmer_id HAVING >> chickens_count < >> 10") >> end >> >> end > > Thanks for the replies. > > This was along the lines of what I was looking for, but I forgot the > most important thing... > > Say chickens have a color, simply a string, such as ''red''. > > How would I order the farmers by the number of red chickens they have? > > Sorry to be such a pain, I should have remembered this the first time > round.add chickens.colour = ''red'', either as a condition or in the join clause. Note that you won''t get back farmers with no chickens, which may or may not be the right thing (if you do, then you should be using a left outer join). Fred> > > Thanks again. > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> add chickens.colour = ''red'', either as a condition or in the join > clause. Note that you won''t get back farmers with no chickens, which > may or may not be the right thing (if you do, then you should be using > a left outer join). > > FredThanks for the quick reply Fred, was thwarted by my failed attempts so far. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---