Hello all! I''ve searched the docs and the mailing list archive but didn''t find the answer to the following: Is there a way to use GROUP BY SQL the same way order is used in the find method or do I need to do a find_by_sql? Thanks for your answers. Bye, Hugo
On 6/14/05, Hugo Magalhaes <hugo.mag-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello all! > I''ve searched the docs and the mailing list archive but didn''t find > the answer to the following: > Is there a way to use GROUP BY SQL the same way order is used in the > find method or do I need to do a find_by_sql?As find won''t use any aggregate functions in the SELECT, there won''t be anything to GROUP BY. So you''ll have to use find_by_sql. Just remember to encapsulate any queries within your model. i.e. class User < ActiveRecord::Base def self.some_useful_method_name find_by_sql("SELECT blah, join blah, group by blah etc.") end end> Thanks for your answers. > > Bye, > Hugo > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
Thanks for your answer Koz. Do you know if I can use my find methods and extract it''s definition to create a find_by_sql? In this way I avoid any writing the complete SQL string. Something like: # tradicional find find_def = :all, :conditions => [ "last_name = ?", last_name], :order => "created_on DESC" Person.find(find_def) # ehanced find by sql Person.find_by_sql(find_def + group by department_id) Maybe that''s asking too much? :-) Thanks again, Hugo On 6/13/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 6/14/05, Hugo Magalhaes <hugo.mag-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Hello all! > > I''ve searched the docs and the mailing list archive but didn''t find > > the answer to the following: > > Is there a way to use GROUP BY SQL the same way order is used in the > > find method or do I need to do a find_by_sql? > > As find won''t use any aggregate functions in the SELECT, there won''t > be anything to GROUP BY. So you''ll have to use find_by_sql. > > Just remember to encapsulate any queries within your model. i.e. > > class User < ActiveRecord::Base > def self.some_useful_method_name > find_by_sql("SELECT blah, join blah, group by blah etc.") > end > end > > > Thanks for your answers. > > > > Bye, > > Hugo > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > -- > Cheers > > Koz >
On 6/14/05, Hugo Magalhaes <hugo.mag-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks for your answer Koz. > > Do you know if I can use my find methods and extract it''s definition > to create a find_by_sql? > In this way I avoid any writing the complete SQL string. > > Something like: > > # tradicional find > find_def = :all, :conditions => [ "last_name = ?", last_name], :order > => "created_on DESC" > Person.find(find_def) > # ehanced find by sql > Person.find_by_sql(find_def + group by department_id) > > Maybe that''s asking too much? :-)I was about to reply saying that unless you have an aggregate function, GROUP BY doesn''t make any sense. However it seems mysql does something truly random with the result set. In short, no rails doesn''t support this. however rails'' find produces pretty defined SQL. just SELECT * from people where ... group by department_id> Thanks again, > Hugo > > > On 6/13/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > On 6/14/05, Hugo Magalhaes <hugo.mag-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Hello all! > > > I''ve searched the docs and the mailing list archive but didn''t find > > > the answer to the following: > > > Is there a way to use GROUP BY SQL the same way order is used in the > > > find method or do I need to do a find_by_sql? > > > > As find won''t use any aggregate functions in the SELECT, there won''t > > be anything to GROUP BY. So you''ll have to use find_by_sql. > > > > Just remember to encapsulate any queries within your model. i.e. > > > > class User < ActiveRecord::Base > > def self.some_useful_method_name > > find_by_sql("SELECT blah, join blah, group by blah etc.") > > end > > end > > > > > Thanks for your answers. > > > > > > Bye, > > > Hugo > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > -- > > Cheers > > > > Koz > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz