toulax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-May-28 11:16 UTC
MySQL Order By Association Size
Is it possible to write this query (or some other query that does essentially the same) using just ActiveRecord.find? SELECT posts.title, COUNT(comments.id) FROM posts, comments WHERE posts.id = comments.post_id GROUP BY posts.id ORDER BY 2 DESC Basically the model Post has many Comments and I want to get the posts with most comments. Also, if possible, I''d also like the query to select posts even if the comment count is 0, with no particular order. Thanks in advance. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
toulax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:> Is it possible to write this query (or some other query that does > essentially the same) using just ActiveRecord.find? > > SELECT posts.title, COUNT(comments.id) FROM posts, comments WHERE > posts.id = comments.post_id GROUP BY posts.id ORDER BY 2 DESC > > Basically the model Post has many Comments and I want to get the posts > with most comments. Also, if possible, I''d also like the query to > select posts even if the comment count is 0, with no particular order. > > Thanks in advance.Hi, Check :include option for find() method: Post.find(:all, :include => ''comments'', :conditions => [''comments.title = ''foo'']) Then you can use comments.column in both :conditions and :order. Jean-Etienne http://www.woa.hu -- 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 -~----------~----~----~----~------~----~------~--~---
toulax-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-May-28 12:12 UTC
Re: MySQL Order By Association Size
Ok, this seems to work: Post.find(:all, :include => [ :comments ], :order => "COUNT(comments.id) DESC", :group => "posts.id") But it seems to return the whole thing on a single array or something, I assumed this is caused by :group, but it messes up my post listing page. Any ideas? On May 28, 8:46 am, Jean-Etienne Durand <rails-mailing-l...@andreas- s.net> wrote:> tou...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > Is it possible to write this query (or some other query that does > > essentially the same) using just ActiveRecord.find? > > > SELECT posts.title, COUNT(comments.id) FROM posts, comments WHERE > > posts.id = comments.post_id GROUP BY posts.id ORDER BY 2 DESC > > > Basically the model Post has many Comments and I want to get the posts > > with most comments. Also, if possible, I''d also like the query to > > select posts even if the comment count is 0, with no particular order. > > > Thanks in advance. > > Hi, > > Check :include option for find() method: > Post.find(:all, :include => ''comments'', :conditions => [''comments.title > = ''foo'']) > Then you can use comments.column in both :conditions and :order. > > Jean-Etiennehttp://www.woa.hu > > -- > 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 don''t see why you need to group by post, there is no need here. -- 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 -~----------~----~----~----~------~----~------~--~---