sql - 将 rails mysql SQL 转换为与 postgresql 一起使用

Question

我有以下标签方法，它根据标签的使用次数选择标签，然后按该顺序排列标签。代码基于 railscasts 剧集:http://railscasts.com/episodes/382-tagging .

我将我的数据库从以前工作的 mysql 更改为 postgres，您可以在堆栈跟踪中看到生成的错误消息。

如何重构此 sql 以与 postgresql 一起使用？

def tags
   Tag.joins(:taggings).select('tags.*, count(tag_id) as "tag_count"').group(:tag_id).order('tag_count desc')
end

堆栈跟踪

ActiveRecord::StatementInvalid - PG::GroupingError: ERROR:  column "tags.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT tags.*, count(tag_id) as "tag_count" FROM "tags" INNE...
               ^
: SELECT tags.*, count(tag_id) as "tag_count" FROM "tags" INNER JOIN "taggings" ON "taggings"."tag_id" = "tags"."id" GROUP BY tag_id ORDER BY tag_count desc:

标签.rb

class Tag < ActiveRecord::Base

  has_many :taggings
  has_many :questions, through: :taggings
  #omitted for brevity

标签.rb

class Tagging < ActiveRecord::Base
  belongs_to :tag
  belongs_to :question
end

如果有人需要更多代码，请大声喊叫。

Answer 1

如错误消息所述，将“tags.id”添加到组子句中非常有效。

Tag.joins(:taggings).select('tags.*, count(tag_id) as "tag_count"').group("tags.id").order('tag_count desc')