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?

Is this Heroku with (dated) PostgreSQL 8.3 or (current) 9.1? Answer depends on the version. Always remember to add such information to your question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 7, 2012 at 0:58
Its the new dev plan: so 9.1 it is. I've added the info to the question. — HectorMalot
– HectorMalot, Commented Aug 7, 2012 at 9:02

Erwin Brandstetter · Accepted Answer · 2021-10-23 22:16:59Z

2

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.

edited Oct 23, 2021 at 22:16

answered Aug 7, 2012 at 14:15

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HectorMalot Over a year ago

Thanks, I have to admit that I switched from the old to the new DB plan yesterday, so I'll check this with PGsql 9.1 soon.

Collectives™ on Stack Overflow

Return a grouped list with occurrences using Rails and PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related