Now I am moving on to Open Translation Project. I’ve done some translation work before, including one of Paul Graham’s essay – Why to not not start a startup. BTW, he finally made a link from the essay to my translation. I used Google Translate as base, but I couldn’t believe how bad the translation was. Yahoo’s Babel Fish was a little better, but not as much. That’s where I got the idea of creating this possibly massive project.

Anyhow, I wanted to find a way of selecting an article or blog that was translated the most. I had one model that stored basic information of original article/blog. Then its children are translations. So, I need to count rows of children with the same parent. In MySQL, I had the following statement in Rails.

@top_origs = OrigPost.find(:all,
                              :select => 'orig_posts.*, count(posts.id) as post_count',
                              :joins => 'left outer join posts on posts.orig_post_id = orig_posts.id',
                              :group => 'orig_posts.id',
                              :order => 'post_count DESC',
                              :limit => 5)

The problem is when I uploaded the changes to Heroku. Since Heroku uses Postgres, and it’s stricter than MySQL, it barfed.

ActiveRecord::StatementInvalid (PGError: ERROR:  column "orig_posts.url" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT orig_posts.*, count(posts.id) as post_count FROM "orig_posts"  left outer join posts on posts.orig_post_id = orig_posts.id  GROUP BY orig_posts.id ORDER BY post_count DESC LIMIT 5):

So, obviously I need to list ALL columns in GROUP BY clause. Yuk.

This is what I did, and it worked.

orig_cols = OrigPost.column_names.collect {|c| "orig_posts.#{c}"}.join(",")
@top_origs = OrigPost.find_by_sql("SELECT #{orig_cols}, count(posts.id) AS post_count FROM orig_posts LEFT OUTER JOIN posts ON posts.orig_post_id = orig_posts.id GROUP BY orig_posts.id, #{orig_cols} ORDER BY post_count DESC LIMIT 5")

Hopefully, this will help others who may have a similar problem.