I find that when using both the group and order parameters with find I get unexpected results. Perhaps I''m doing this incorrectly OR perhaps my expectations just need adjusting. Either way, here''s what I''m doing: class State < ActiveRecord::Base has_one :country end class Country < ActiveRecord::Base has_many :states end State.find(:all, :include => :country, :group => ''countries.id'', :order => ''states.name'') I expected to have received a collection of states grouped by country and ordered by the state''s name *within each group*. Instead I received a collection that was just ordered by the state''s name. Is there anyway, besides the obvious find_by_sql DIY approach to get my desired results using find? Regards, Jason -- 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 -~----------~----~----~----~------~----~------~--~---
Jason Fox wrote: Sorry, correction: class State < ActiveRecord::Base belongs_to :country end -- 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 -~----------~----~----~----~------~----~------~--~---
On 8/22/06, Jason Fox <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > > I find that when using both the group and order parameters with find I > get unexpected results. Perhaps I''m doing this incorrectly OR perhaps > my expectations just need adjusting. Either way, here''s what I''m doing: > > class State < ActiveRecord::Base > belongs_to :country > end > > class Country < ActiveRecord::Base > has_many :states > end > > > State.find(:all, :include => :country, :group => ''countries.id'', :order > => ''states.name'') > > I expected to have received a collection of states grouped by country > and ordered by the state''s name *within each group*. Instead I received > a collection that was just ordered by the state''s name. Is there > anyway, besides the obvious find_by_sql DIY approach to get my desired > results using find?Not quite sure what you''re looking for, but the first thing you might want to do is to declare ''Country'' as: class Country < ActiveRecord::Base has_many :states, :order => ''name'' end Then saying Country.find(:all, :include => :state) will load all the state/country pairs in one query. The SQL terminology is not 100% transparent - "GROUP BY" is used to create partial results when using aggregate functions. A simple example would be using SUM() to create totals - without grouping, the result is the total of all the selected rows. With grouping, the result will be a set of subtotals. Hope this helps, Matt Jones mdj.acme-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org President/Technical Director, Acme Art Company, acmeartco.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Jason Fox wrote:> I expected to have received a collection of states grouped by country > and ordered by the state''s name *within each group*. Instead I received > a collection that was just ordered by the state''s name. Is there > anyway, besides the obvious find_by_sql DIY approach to get my desired > results using find?GROUP BY collects all the result rows with the same countries.id and smashes them into a single result row (as Matt said, useful for doing aggregate functions, like SUM and COUNT). You want to just ORDER BY countries.id, states.name ASE -- 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 -~----------~----~----~----~------~----~------~--~---