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
-~----------~----~----~----~------~----~------~--~---