# Sql help...getting user with the most recipes from table? **URL:** \ **Category:** Rails\ **Created:** [December 20, 2006, 9:18pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013 "2006-12-20T21:18:09Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 20, 2006, 9:18pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/1 "2006-12-20T21:18:09Z")
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. ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 20, 2006, 9:29pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/2 "2006-12-20T21:29:56Z")
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 ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 20, 2006, 10:01pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/3 "2006-12-20T22:01:29Z")
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 ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 20, 2006, 11:22pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/4 "2006-12-20T23:22:38Z")
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.](http://www.rubyonrails.org/api/classes/ActiveRecord/Associations/ClassMethods.html). ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 20, 2006, 10:48pm UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/5 "2006-12-20T22:48:25Z")
> @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](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 ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 21, 2006, 12:05am UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/6 "2006-12-21T00:05:26Z")
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. ---
**Author:** ![poipu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9dc877/32.png) [@poipu](https://www.ruby-forum.com/u/poipu)\ **Post date:** [December 21, 2006, 6:44am UTC](https://www.ruby-forum.com/t/sql-help-getting-user-with-the-most-recipes-from-table/80013/7 "2006-12-21T06:44:59Z")
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”)