将表列添加到group by子句 - Ruby on Rails - Postgresql [英] Adding Table Columns to a Group by clause - Ruby on Rails - Postgresql

问题描述

我正在尝试使用Heroku，显然PostgreSQL比集合函数的SQL要严格得多。当我推到Heroku时，我收到一个错误，指出下面的内容。

关于另一个问题，我问我收到了一些指导，说我应该将列添加到我的group by子句中，而我不知道如何去做。请参阅下面的完整错误和PostsControll＃索引。

  SELECT posts。*，count（*）as vote_total FROMpostsINNER JOINvotesON votes.post_id = posts .id GROUP BY votes.post_id ORDER BY created_at DESC LIMIT 5 OFFSET 0）：
  

PostsController

  def index 
 @tag_counts = Tag.count（：group =>：tag_name，
：order => ;'count_all DESC'，：limit => 20）
 conditions，joins = {}，：votes 
 
 @ugtag_counts = Ugtag.count（：group =>：ugctag_name，
：order =>'count_all DESC'，：limit => 20）
 conditions，joins = {}，：votes 
 
 @vote_counts = Vote.count（：group =>：post_title，
：order =>'count_all DESC'，：limit => 20）
 conditions，joins = {}，：votes 
 
 
除非（params [：tag_name] ||）.empty？ 
 conditions = [tags.tag_name =？，params [：tag_name]] 
 joins = [：tags，：votes] 
 end 
 @ posts = Post.paginate（ 
：select =>posts。*，count（*）as vote_total，
：joins => join，
：conditions => conditions，
：group =>votes.post_id，
：order =>created_at DESC，
：page => params [：page]，：per_page => 5）
 @ popular_posts = Post.paginate（
：select =>posts。*，count（*）as vote_total，
：joins => joins，
：conditions => conditions， 
：group =>votes.post_id，
：order =>vote_total DESC，
：page => params [：page]，：per_page => 3 ）
 
 respond_to | format | 
 format.html＃index.html.erb 
 format.xml {render：xml => @posts} 
 format.json {render：json => @posts} 
 format.atom 
 end 
 end 
  

解决方案MySQL和SQLite非常灵活，它们允许选择列中的列不被命名在 GROUP BY 子句中（而不是在聚合函数，如 COUNT（））。但是这种灵活性会导致模糊的查询。

PostgreSQL只和ANSI SQL标准一样严格。我测试过的所有其他数据库（Oracle，Microsoft，IBM DB2，Firebird）在这个问题上的行为与PostgreSQL相似。

您需要做的是将<您的选择列表中的code> posts 列匹配您的 GROUP BY 子句中指定的列。通过选择较少的列或通过向：group 添加列来实现这一点。我不是Rails专家，我无法找到如何将多个列作为参数传递给：group 的示例。查看active_record / base.rb的代码，它似乎只是将选项值复制到文字SQL GROUP BY 子句中。所以我会假设（没有尝试过）你可以这样做：

  ... 
：group = > posts.post_id，posts.foo，posts.bar，posts.baz，
 ... 
  

请注意，您必须为您的选择列表中不属于集合函数的部分命名每个列。

I am trying to use Heroku and apparently Postgresql is a lot more strict than SQL for aggregate functions. When I am pushing to Heroku I am getting an error stating the below.

On another question I asked I received some guidance that said I should just add the columns to my group by clause and I am not sure how to do that. See the full error below and the PostsControll#index.

SELECT posts.*, count(*) as vote_total FROM "posts"   INNER JOIN "votes" ON votes.post_id = posts.id   GROUP BY votes.post_id ORDER BY created_at DESC LIMIT 5 OFFSET 0):

PostsController

def index
    @tag_counts = Tag.count(:group => :tag_name, 
       :order => 'count_all DESC', :limit => 20)
       conditions, joins = {}, :votes

    @ugtag_counts = Ugtag.count(:group => :ugctag_name, 
       :order => 'count_all DESC', :limit => 20)
       conditions, joins = {}, :votes

    @vote_counts = Vote.count(:group => :post_title, 
          :order => 'count_all DESC', :limit => 20)
          conditions, joins = {}, :votes


       unless(params[:tag_name] || "").empty?
         conditions = ["tags.tag_name = ? ", params[:tag_name]]
         joins = [:tags, :votes]
       end
       @posts=Post.paginate(
                 :select => "posts.*, count(*) as vote_total", 
                 :joins => joins, 
                 :conditions=> conditions, 
                 :group => "votes.post_id", 
                 :order => "created_at DESC",
                 :page => params[:page], :per_page => 5)
        @popular_posts=Post.paginate(
                 :select => "posts.*, count(*) as vote_total", 
                 :joins => joins, 
                 :conditions=> conditions, 
                 :group => "votes.post_id", 
                 :order => "vote_total DESC",
                 :page => params[:page], :per_page => 3)

    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @posts }
      format.json { render :json => @posts }
      format.atom
    end
  end

解决方案

MySQL and SQLite are so flexible that they allow columns in the select-list without being named in the GROUP BY clause (and not inside an aggregate function like COUNT()). But this level of flexibility can lead to ambiguous queries.

PostgreSQL is only as strict as the ANSI SQL standard. All other databases I've tested (Oracle, Microsoft, IBM DB2, Firebird) behave like PostgreSQL on this issue.

What you need to do is make the list of posts columns in your select-list match the columns named in your GROUP BY clause. Do this either by selecting fewer columns, or by adding columns to :group.

I'm not a Rails expert, and I can't find an example of how to pass multiple columns as an argument to :group. Looking at the code for active_record/base.rb, it appears that it simply copies the option value into a literal SQL GROUP BY clause. So I would suppose (without having tried it) that you could do this:

...
:group => "posts.post_id, posts.foo, posts.bar, posts.baz", 
...

Note that you have to name every column that you have in your select-list that is not part of an aggregate function.

这篇关于将表列添加到group by子句 - Ruby on Rails - Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！