返回分组列表，用Rails和PostgreSQL事件 [英] Return a grouped list with occurrences using Rails and PostgreSQL
问题描述
我有标签在我的Rails应用程序列表，它链接到文章使用的Tagging 。在一些观点，我想的时候，他们已经被标记显示5最常用的标签列表，在一起。要创建一个完整的例子，假设一个表有3个职位：
邮电
ID |标题
1 | LOREM
2 |存有
3 |悲
和带2个标签表
标签
ID |名称
1 |标签1
2 |标签2
现在，如果后1被打上了Tag1中和后2被打上了标记1和2，我们的Tagging表看起来是这样的：
的Tagging
tag_id |的post_id
1 | 1
1 | 2
2 | 2
接下来的问题是如何（pferably $ P $，而不必返回到数据库多次）获取所需的信息显示如下结果：
常用标签：
TAG1（2次）
标签2（1次）
我已经设法通过包括标签要做到这一点使用MySQL，分组由 tag_id 和订购的数量：
红宝石：
的Tagging = Tagging.select（*，计数（tag_id）AS数），包括（是：标签）。集团（：tag_id）.order（'COUNT（*）DESC '）.limit（5）
taggings.each {| T |把＃{t.tag.name}（＃{t.count}）}
不过，我移动应用程序到Heroku的，因此，我需要移动到PostgreSQL 9.1。不幸的Postgres的严格打破了查询，因为它需要的所有领域的的组由规定的条款。我试着去的方式，但它造成的事实，我不能使用 t.count 再得到的行数。
所以，最后得到了一个问题：
什么是查询这种由Postgres的（9.1）的信息，并将其显示给用户的最佳方式？
不幸的Postgres的严格打破，因为它是查询 要求所有字段进行了group by子句中指定。
现在，已经在PostgreSQL 9.1有所改变（引用的 9.1 发行说明）：
当主允许非GROUP BY列的查询目标列表中 关键是BY子句指定在GROUP（彼得Eisentraut）
更重要的是，基本的查询您所描述甚至不会遇到这样的：
我想显示5个最常用的标签列表，一起 随着时代的他们已经被标记
SELECT tag_id，分别COUNT（*）AS倍
FROM的Tagging
GROUP BY tag_id
ORDER BY时间降序
LIMIT 5;
适用于任何情况。
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?
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:
I'd like to 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.
这篇关于返回分组列表，用Rails和PostgreSQL事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
