# Sort and Paginate articles by # number of comments

**URL:** <https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641>\
**Category:** Rails\
**Created:** [October 25, 2006, 10:12pm UTC](https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641 "2006-10-25T22:12:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Heri\_R](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/58956e/32.png) [@Heri\_R](https://www.ruby-forum.com/u/Heri_R)\
**Post date:** [October 25, 2006, 10:12pm UTC](https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641/1 "2006-10-25T22:12:41Z")

</div>

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](http://sprinj.com)

---

<div class="post-metadata">

**Author:** ![Heri\_R](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/58956e/32.png) [@Heri\_R](https://www.ruby-forum.com/u/Heri_R)\
**Post date:** [October 25, 2006, 10:56pm UTC](https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641/2 "2006-10-25T22:56:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Heri\_R](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/58956e/32.png) [@Heri\_R](https://www.ruby-forum.com/u/Heri_R)\
**Post date:** [October 26, 2006, 1:14am UTC](https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641/3 "2006-10-26T01:14:45Z")

</div>

Jason N. 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

---

<div class="post-metadata">

**Author:** ![Heri\_R](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/58956e/32.png) [@Heri\_R](https://www.ruby-forum.com/u/Heri_R)\
**Post date:** [October 26, 2006, 3:25am UTC](https://www.ruby-forum.com/t/sort-and-paginate-articles-by-number-of-comments/73641/4 "2006-10-26T03:25:05Z")

</div>

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](http://sprinj.com)