使用 Rails 和 PostgreSQL 返回包含出现次数的分组列表 [英] Return a grouped list with occurrences using Rails and PostgreSQL

问题描述

我的 rails 应用程序中有一个 Tags 列表，这些列表使用 Taggings 链接到 Posts.在某些视图中，我想显示 5 个最常用标签的列表，以及它们被标记的时间.要创建一个完整的示例，假设有一个包含 3 个帖子的表:

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

一张有 2 个标签的表格

And a table with 2 Tags

TAGS
ID | name
1  | Tag1
2  | Tag2

现在，如果帖子 1 被标记为 Tag1，而帖子 2 被标记为标记 1 和 2，我们的标记表如下所示:

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

那么问题就是如何获取需要的信息(最好不要多次回DB)显示如下结果:

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)

我通过包含标签、按 tag_id 分组和按计数排序，使用 MySQL 设法做到了这一点:

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

红宝石:

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})" }

但是，我将应用程序移至 Heroku，因此我需要移至 PostgreSQL 9.1.不幸的是，Postgres 的严格性破坏了该查询，因为它要求在 group by 子句中指定所有字段.我试过那样做，但结果是我不能再使用 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.

所以，最终得到问题:

从 postgres (v9.1) 查询此类信息并将其显示给用户的最佳方法是什么?

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

推荐答案

您的问题:

不幸的是，Postgres 的严格性破坏了该查询，因为它要求在 group by 子句中指定所有字段.

现在，随着 PostgreSQL 9.1(引用 9.1 的发行说明):

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

允许非GROUP BY<查询目标列表中的/code> 列键在 GROUP BY 子句中指定 (Peter Eisentraut)

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:

一起显示 5 个最常用的标签列表他们被标记的时间.

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

在任何情况下都有效.

这篇关于使用 Rails 和 PostgreSQL 返回包含出现次数的分组列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！