I have a table that I want to do a query on that looks something like this select *,count(flied2) as count from exampletable where field1 is null group by field2,field3 order by count desc I want to select all the rows where field1 is null, grouping them together by field2, with subgroups by field3, with counts of how many are each group so that I can display grouping patterns to the user. My model doesn''t have a count attribute so I can''t just do this: @models = Model.find(:all,:conditions=>"....") What is the best way to do this? 1. Add a count attribute to my model 2. Something else? - Michael
Michael King wrote:> I have a table that I want to do a query on that looks something like this > > select *,count(flied2) as count from exampletable where field1 is null > group by field2,field3 order by count desc > > I want to select all the rows where field1 is null, grouping them > together by field2, with subgroups by field3, with counts of how many > are each group so that I can display grouping patterns to the user. > > My model doesn''t have a count attribute so I can''t just do this: > > @models = Model.find(:all,:conditions=>"....") > > What is the best way to do this? > 1. Add a count attribute to my model > 2. Something else?Can''t say if this is *best*, but you can use find_by_sql, with whtever SQL you need to get your resuls; the returned objects will have acquired a count attribute (if you''ve specified that, as in your example) That was my experience a month or two ago when I tried this same thing. James -- http://www.ruby-doc.org - The Ruby Documentation Site http://www.rubyxml.com - News, Articles, and Listings for Ruby & XML http://www.rubystuff.com - The Ruby Store for Ruby Stuff http://www.jamesbritt.com - Playing with Better Toys
On 7/28/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have a table that I want to do a query on that looks something like this > > select *,count(flied2) as count from exampletable where field1 is null > group by field2,field3 order by count desc > > I want to select all the rows where field1 is null, grouping them > together by field2, with subgroups by field3, with counts of how many > are each group so that I can display grouping patterns to the user. > > My model doesn''t have a count attribute so I can''t just do this: > > @models = Model.find(:all,:conditions=>"....") > > What is the best way to do this? > 1. Add a count attribute to my model > 2. Something else? > > - MichaelIf I recall correctly, uou can do Model.find_by_sql( "SELECT *,count(field2) as count FROM tablefoo" )
Thanks for the responses, however I should have clarified that I know how to do the find_by_sql, what I am wondering is how do I handle the results if the results has a field that my model does not. - Michael On 7/28/05, Belorion <belorion-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/28/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I have a table that I want to do a query on that looks something like this > > > > select *,count(flied2) as count from exampletable where field1 is null > > group by field2,field3 order by count desc > > > > I want to select all the rows where field1 is null, grouping them > > together by field2, with subgroups by field3, with counts of how many > > are each group so that I can display grouping patterns to the user. > > > > My model doesn''t have a count attribute so I can''t just do this: > > > > @models = Model.find(:all,:conditions=>"....") > > > > What is the best way to do this? > > 1. Add a count attribute to my model > > 2. Something else? > > > > - Michael > > If I recall correctly, uou can do Model.find_by_sql( "SELECT > *,count(field2) as count FROM tablefoo" ) > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks for the responses, however I should have clarified that I know > how to do the find_by_sql, what I am wondering is how do I handle the > results if the results has a field that my model does not. > > - MichaelYou should be able to do foo = Model.find_by_sql( "SELECT *, count(field1) as mycount FROM table" ) and then simply do a foo.mycount.
On 7/29/05, Belorion <belorion-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Thanks for the responses, however I should have clarified that I know > > how to do the find_by_sql, what I am wondering is how do I handle the > > results if the results has a field that my model does not. > > > > - Michael > > You should be able to do foo = Model.find_by_sql( "SELECT *, > count(field1) as mycount FROM table" ) and then simply do a > foo.mycount.ActiveRecord maps columns to attributes automatically (in the biz, we call this ''piggybacking''). However, I''m not sure it will get the type correct since ''mycount'' isn''t part of the table definition. Model.find_by_sql is the wrong method to use in this instance. Use count or count_by_sql. User.count User.count ''active = 1'' User.count_by_sql ''SELECT COUNT(*) FROM users'' API Links: http://rails.rubyonrails.com/classes/ActiveRecord/Base.html#M000683 http://rails.rubyonrails.com/classes/ActiveRecord/Base.html#M000684 I wouldn''t use Model.find_* unless you''re actually selecting a model. I''d actually use Model.connection.select_one for other aggregate queries (MAX, MIN, SUM, etc). http://rails.rubyonrails.com/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html -- rick http://techno-weenie.net
Thanks, I just found the same answer in a Hibernate vs Rails article of all places... - Michael On 7/29/05, Belorion <belorion-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Thanks for the responses, however I should have clarified that I know > > how to do the find_by_sql, what I am wondering is how do I handle the > > results if the results has a field that my model does not. > > > > - Michael > > You should be able to do foo = Model.find_by_sql( "SELECT *, > count(field1) as mycount FROM table" ) and then simply do a > foo.mycount. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Michael King wrote:> Thanks, I just found the same answer in a Hibernate vs Rails article > of all places...And what did the article say? James -- http://www.ruby-doc.org - The Ruby Documentation Site http://www.rubyxml.com - News, Articles, and Listings for Ruby & XML http://www.rubystuff.com - Now with Rails Stuff! http://www.jamesbritt.com - Playing with Better Toys
Could you post the URL that has the answer in case other people (like
me) are curious?
George
--
George Hotelling GPG: 0x8175D485 ]
http://george.hotelling.net ]
If your site doesn''t RSS, I won''t visit twice. ]
_ _ _ ___ _ _ _/
On Jul 29, 2005, at 10:06 AM, Michael King wrote:
> Thanks, I just found the same answer in a Hibernate vs Rails article
> of all places...
>
> - Michael
The article discussing Hibernate vs Rails is at: http://www.browsermedia.com/devcorner/whitepapers/hibernate_vs_rails.jsp The answer to my question was towards the end of the section Rails Insta-Finders - Michael On 7/29/05, George Hotelling <george-s1Z0Dg4i37rMFIMGWPqnnw@public.gmane.org> wrote:> Could you post the URL that has the answer in case other people (like > me) are curious? > > George > -- > George Hotelling GPG: 0x8175D485 ] > http://george.hotelling.net ] > If your site doesn''t RSS, I won''t visit twice. ] > _ _ _ ___ _ _ _/ > > On Jul 29, 2005, at 10:06 AM, Michael King wrote: > > > Thanks, I just found the same answer in a Hibernate vs Rails article > > of all places... > > > > - Michael > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Hrm. I''m not sure when that article was written, but it seems that the author doesn''t know about the :include keyword. Michael King wrote:> The article discussing Hibernate vs Rails is at: > http://www.browsermedia.com/devcorner/whitepapers/hibernate_vs_rails.jsp > > The answer to my question was towards the end of the section Rails Insta-Finders > > - Michael > > On 7/29/05, George Hotelling <george-s1Z0Dg4i37rMFIMGWPqnnw@public.gmane.org> wrote: > >>Could you post the URL that has the answer in case other people (like >>me) are curious? >> >>George >>-- >>George Hotelling GPG: 0x8175D485 ] >>http://george.hotelling.net ] >> If your site doesn''t RSS, I won''t visit twice. ] >> _ _ _ ___ _ _ _/ >> >>On Jul 29, 2005, at 10:06 AM, Michael King wrote: >> >> >>>Thanks, I just found the same answer in a Hibernate vs Rails article >>>of all places... >>>