sql - Return a grouped list with occurrences using Rails and PostgreSQL

Question

I have a list of Tags in my rails application which are linked to Posts using Taggings. In some views, I'd like to show a list of the 5 most commonly used tags, together with the times they have been tagged. To create a complete example, assume a table with 3 posts:

POSTS
ID | title
1  | Lorem
2  | Ipsum
3  | Dolor

And a table with 2 Tags

TAGS
ID | name
1  | Tag1
2  | Tag2

Now, if Post 1 is tagged with Tag1 and post 2 is tagged with tags 1 and 2, our taggings table looks like this:

TAGGINGS
tag_id | post_id
1      | 1
1      | 2
2      | 2

Then the question is how to fetch the required information (preferably without going back to the DB multiple times) to display the following result:

Commonly used tags:
tag1 (2 times)
tag2 (1 time)

I've managed to do this using MySQL by including the tag, grouping by tag_id and ordering by the count:

Ruby:

taggings = Tagging.select("*, count(tag_id) AS count").includes(:tag).group(:tag_id).order('count(*) DESC').limit(5)
taggings.each { |t| puts "#{t.tag.name} (#{t.count})" }

However, I'm moving the app to Heroku, and therefore I'm required to move to PostgreSQL 9.1. Unfortunately the strictness of Postgres breaks that query because it requires all fields to be specified in the group by clause. I've tried going that way, but it has resulted in the fact that I can't use t.count anymore to get the count of the rows.

So, to finally get to the question:

What is the best way to query this kind of information from postgres (v9.1) and display it to the user?

Answer 1

Your problem:

Unfortunately the strictness of Postgres breaks that query because it requires all fields to be specified in the group by clause.

Now, that has changed somewhat with PostgreSQL 9.1 (quoting release notes of 9.1):

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

What's more, the basic query you describe would not even run into this:

Show a list of the 5 most commonly used tags, together with the times they have been tagged.

SELECT tag_id, count(*) AS times
FROM   taggings
GROUP  BY tag_id
ORDER  BY times DESC
LIMIT  5;

Works in any case.