Top 10 in mysql?

Rails
1

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

2

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!

3

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?

4

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.

5

Thank you very much, I got it working!