Hi, I''m new to rails and I''m trying to find the top ten posted url in my mysql db... so let''s say my db name is links and each item has two fields (userid and url)... I want to find the top 10 posted url by count... how can I do that? I know I have to use the find() method but I''m having trouble with count()... thanks in advance --~--~---------~--~----~------------~-------~--~----~ 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, I''m new to rails and I''m trying to find the top ten posted url in > my mysql db... so let''s say my db name is links and each item has two > fields (userid and url)... I want to find the top 10 posted url by > count... how can I do that? I know I have to use the find() method but > I''m having trouble with count()...Assuming you are using a sql database, the non-rails way to do this is: select url, count(url) from my_table group by url limit 10 Using Active Record you need to look atthe :group and :limit attributes. Good luck! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
thanks for your response but I want to do it by Active Record... I have tried using :group but my object is always nil... @links = Links.find(:all, :group => "url", :limit => 10) using mysql it would be something like this : select count(url) as total, url from links group by url order by total DESC limit 10 How does this translate in Active recod? On Aug 7, 10:30 am, "toby privett" <tobypriv...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hi, I''m new to rails and I''m trying to find the top ten posted url in > > my mysql db... so let''s say my db name is links and each item has two > > fields (userid and url)... I want to find the top 10 posted url by > > count... how can I do that? I know I have to use the find() method but > > I''m having trouble with count()... > > Assuming you are using a sql database, the non-rails way to do this is: > select url, count(url) > from my_table > group by url > limit 10 > > Using Active Record you need to look atthe :group and :limit attributes. > > Good luck!--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Sketchy, a couple of things, one calling find() on a model object
expects to be able to return to you a set of model objects. A
count (or sum, ...) aren''t generally in your model so its
hard to coerce the select into a model form.
Second, you have to tell the database sometime to
actually do some counting. The following would work if you
have in your Links table some number column called
"counter"
@links = Links.find(:all, :select => "url, count(*) as counter",
:group => "url",
:order => "counter desc",
:limit => 10)
Note that you need the order clause because it will
sort all the grouped links in decending order, then
you need limit to only get the top 10.
Also not that you must return column names that fit into the
model Links - otherwise model objects can''t be returned.
On Aug 7, 11:40 pm, Sketchy
<logi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> thanks for your response but I want to do it by Active Record... I
> have tried using :group but my object is always nil...
>
> @links = Links.find(:all, :group => "url", :limit => 10)
>
> using mysql it would be something like this :
> select count(url) as total, url from links group by url order by total
> DESC limit 10
>
> How does this translate in Active recod?
>
> On Aug 7, 10:30 am, "toby privett"
<tobypriv...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > Hi, I''m new to rails and I''m trying to find the
top ten posted url in
> > > my mysql db... so let''s say my db name is links and each
item has two
> > > fields (userid and url)... I want to find the top 10 posted url
by
> > > count... how can I do that? I know I have to use the find()
method but
> > > I''m having trouble with count()...
>
> > Assuming you are using a sql database, the non-rails way to do this
is:
> > select url, count(url)
> > from my_table
> > group by url
> > limit 10
>
> > Using Active Record you need to look atthe :group and :limit
attributes.
>
> > Good luck!
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Thank you very much, I got it working! On Aug 7, 7:59 pm, Kip <kipco...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Sketchy, a couple of things, one calling find() on a model object > expects to be able to return to you a set of model objects. A > count (or sum, ...) aren''t generally in your model so its > hard to coerce the select into a model form. > > Second, you have to tell the database sometime to > actually do some counting. The following would work if you > have in your Links table some number column called > "counter" > > @links = Links.find(:all, :select => "url, count(*) as counter", > :group => "url", > :order => "counter desc", > :limit => 10) > > Note that you need the order clause because it will > sort all the grouped links in decending order, then > you need limit to only get the top 10. > > Also not that you must return column names that fit into the > model Links - otherwise model objects can''t be returned. > > On Aug 7, 11:40 pm, Sketchy <logi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > thanks for your response but I want to do it by Active Record... I > > have tried using :group but my object is always nil... > > > @links = Links.find(:all, :group => "url", :limit => 10) > > > using mysql it would be something like this : > > select count(url) as total, url from links group by url order by total > > DESC limit 10 > > > How does this translate in Active recod? > > > On Aug 7, 10:30 am, "toby privett" <tobypriv...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > Hi, I''m new to rails and I''m trying to find the top ten posted url in > > > > my mysql db... so let''s say my db name is links and each item has two > > > > fields (userid and url)... I want to find the top 10 posted url by > > > > count... how can I do that? I know I have to use the find() method but > > > > I''m having trouble with count()... > > > > Assuming you are using a sql database, the non-rails way to do this is: > > > select url, count(url) > > > from my_table > > > group by url > > > limit 10 > > > > Using Active Record you need to look atthe :group and :limit attributes. > > > > Good luck!--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---