Adding Table Columns to a Group by clause - Ruby on Rails - Postgresql

Question

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

Answer 1

Have you tried just switching to using posts.id eg:

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

If so, how does the result set then differ from what you're getting with your existing SQL?

Answer 2

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.