Returns a grouped list with entries using Rails and PostgreSQL

Question

I have a list of Tags in my rails application that are related to Posts using Taggings . In some submissions, I would like to show a list of the 5 most used tags, as well as the time when they were tagged. To create a complete example, suppose a table with three messages:

 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 1 and 2, our tag table looks like this:

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

Then the question arises, how to obtain the required information (preferably without returning to the database several times) in order to display the following result:

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

I managed to do this using MySQL by including a tag, grouping tag_id and ordering the score:

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 am moving the application to Heroku, and so I need to switch to PostgreSQL 9.1. Unfortunately, the strictness of Postgres violates this request because it requires that all fields be specified in the group by clause. I tried to go this way, but this led to the fact that I can no longer use t.count to get the number of rows.

So, to finally ask a question:

What is the best way to request such information from postgres (v9.1) and display it to the user?

Answer 1

Your problem:

Unfortunately, the strictness of Postgres violates this request because it requires that all fields be specified in the group by clause.

Now this has changed a bit from PostgreSQL 9.1 (citing release notes for 9.1 ):

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

What more, the main request that you described will not even run in this:

Show a list of the 5 most commonly used tags, along with the time they were tagged.

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

It works anyway.