Sql help...getting user with the most recipes from table?

Rails
1

hi, i need to fetch the users with the most airtcles in my table.

i am starting with this…

@temp = Howto.find_by_sql ([“select user_id from Howtos” ])…

now i have an array with all the user_ids…but i need to do something
like this…

@temp = Howto.find_by_sql ([“select user_id from Howtos Order by
user_id.count limit 10” ])…

…any suggestions? thanks.

2

I assume a user has_many howtos and a howto belongs_to a user in the
respective models

@temp = User.find(:all, :select => “users.id,users.username,
count(howtos.id)
as numhowtos”, :joins => “left join howtos on users.id =
howtos.user_id”,
:order => “numhowtows DESC”)

Adam

3

hi thanks for the help! unfortunantley my debugging skills with sql is
limited. i am recieving this error

Mysql::Error: #42000Mixing of GROUP columns (MIN(),MAX(),COUNT(),…)
with no GROUP columns is illegal if there is no GROUP BY clause: SELECT
users.id, users.login, count(howtos.user_id) as numhowtos FROM users
left join howtos on users.id = howtos.user_id ORDER BY numhowtos DESC

i am using this

@temp = User.find(:all,
:select => “users.id, users.login,
count(howtos.user_id) as numhowtos”,
:joins => “left join howtos on users.id =
howtos.user_id”,
:order => “numhowtos DESC”
)

users is my table name and each user hasn many howtos and howto belongs
to user…

thanks!

unknown wrote:

I assume a user has_many howtos and a howto belongs_to a user in the
respective models

@temp = User.find(:all, :select => “users.id,users.username,
count(howtos.id)
as numhowtos”, :joins => “left join howtos on users.id =
howtos.user_id”,
:order => “numhowtows DESC”)

Adam

4

You might want to implement counter_cache to store these values instead
of calculating them each time.
See
Ruby on Rails — A web-app framework that includes everything needed to create database-backed web applications according to the Model-View-Controller (MVC) pattern..

5

@temp = Howto.find_by_sql ([“select user_id from Howtos Order by
user_id.count limit 10” ])…

Here is a description of how to use MySQL count() function.
http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

You probably want to do something like:

select count(user_id), user_id from Howtos
group by user_id

This is an aggregate query, so you’d use GROUP
rather than ORDER. hth

6

i tried this…

@topusers= User.find_by_sql(“SELECT users.id,users.login,
COUNT(#{Howto.id}) FROM Users GROUP BY users.id”)

but my count for each user is set to one and not the proper count…

when i change the Howto.id to Howto.user_id i get a ’ stack level too
deep’… i also implemented the method

def user_id
user_id
end

in the howto model class.

7

hey everyone…thanks for the help… i finally found something that
seems to work but i dont quite understand yet how mysql works…

this is what i did…

User.find_by_sql (“SELECT Users.id, COUNT(Howtos.user_id) FROM Howtos,
Users WHERE Howtos.user_id = Users.id GROUP BY Users.id”)