Rails - Oct 2006 - Sort and Paginate articles by # number of comments

hello list,

i am programming a function to sort articles by the number of comments they
had today but i keep having sql errors
i want to use something like (doesnt work actually)

@articles = Articles.find(:all, :include => ''comments'',
:conditions => [''
comments.created_at >'', Time.now - 86400], :order =>
''COUNT(comments.id) '')

it is in :order i dont really know what to put in

i can do it in about 2 lines without the :order and then with a sort! but i
want now to paginate so i need to wrap it all in 1 sql query.
??

thanks

-- 
Heri R.
http://sprinj.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
-~----------~----~----~----~------~----~------~--~---

This one was a brainbuster. Seriously. I''m lucky it''s the end
of the
work day because I''m done.
This is a beast of a query, and with the 200,000 row child table I 
tested it on, it took a while.

    select *
    from articles
    inner join comments on articles.id = comments.article_id
    group by articles.id
    order by count(*) DESC;

BAM.

Heri R> wrote:
> hello list,
>
> i am programming a function to sort articles by the number of comments 
> they had today but i keep having sql errors
> i want to use something like (doesnt work actually)
>
> @articles = Articles.find(:all, :include =>
''comments'', :conditions =>
> ['' comments.created_at >'', Time.now - 86400], :order
=>
> ''COUNT(comments.id <http://comments.id>) '')
>
> it is in :order i dont really know what to put in
>
> i can do it in about 2 lines without the :order and then with a sort! 
> but i want now to paginate so i need to wrap it all in 1 sql query.
> ??
>
> thanks
>
> -- 
> Heri R.
> http://sprinj.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
-~----------~----~----~----~------~----~------~--~---

Jason Norris wrote:
> This one was a brainbuster. Seriously. I''m lucky it''s the
end of the
> work day because I''m done.
> This is a beast of a query, and with the 200,000 row child table I
> tested it on, it took a while.
> 
>     select *
>     from articles
>     inner join comments on articles.id = comments.article_id
>     group by articles.id
>     order by count(*) DESC;
> 
> BAM.
I''m sure the biggest bottleneck here is the 200,000 record resultset. 
ActiveRecord will choke on this large of a resultset. The query itself 
probably runs pretty fast.  I actually had to give up on using 
ActiveRecord (and eventually Ruby altogether) for a project because of 
this. I bet if you include a LIMIT/OFFSET (pagination) in there it''ll 
run a lot faster.

-matthew

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

thanks jason, but doesnt seem to work, i get a "#HY000 Invalid use of group
function" error

Article.find_by_sql(select articles.*, count(comments.id)
    inner join comments on articles.id = comments.article_id
    group by articles.id WHERE comments.created_at > ...yesterdaysDate...
    order by 2 DESC)

works though  (instead of order by count(*)

-- 
Heri R.
http://sprinj.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
-~----------~----~----~----~------~----~------~--~---