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 >', - 86400], :order => 'COUNT( ')

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.



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 = comments.article_id     group by     order by count(*) DESC;


Heri R> wrote:

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

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

works though (instead of order by count(*)