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?
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 theGROUP 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.