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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
straightflush-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2006-Dec-20 20:29 UTC
Re: sql help...getting user with the most recipes from table?
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 On 12/20/06, poipu <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > > 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. > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ 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 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-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
toby privett
2006-Dec-20 21:46 UTC
Re: sql help...getting user with the most recipes from table?
>@temp = Howto.find_by_sql (["select user_id from Howtos Order byuser_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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
askegg
2006-Dec-20 22:20 UTC
Re: sql help...getting user with the most recipes from table?
You might want to implement counter_cache to store these values instead of calculating them each time. See http://www.rubyonrails.org/api/classes/ActiveRecord/Associations/ClassMethods.html. On Dec 21, 7:18 am, poipu <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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. > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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") -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---