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)
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;
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.
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)