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

Bill Karwin · Accepted Answer · 2010-04-23 17:22:40Z

3

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.

answered Apr 23, 2010 at 17:22

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bgadoci Over a year ago

That did it. Thanks you so much. About to post next issue as a separate question.

the Tin Man Over a year ago

I've always used a simple string to list the columns being grouped.

Taryn East · Accepted Answer · 2010-04-23 15:35:56Z

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?

answered Apr 23, 2010 at 15:35

Taryn East

27.8k9 gold badges89 silver badges110 bronze badges

3 Comments

bgadoci Over a year ago

I tried just changing the :group => "votes.post_id" to post ID but when clicking that group and sending the URL w/ tag_name, it breaks. Here is the error: SQLite3::SQLException: ambiguous column name: post_id: SELECT posts.*, count(*) as vote_total FROM "posts" INNER JOIN "tags" ON tags.post_id = posts.id INNER JOIN "votes" ON votes.post_id = posts.id WHERE (tags.tag_name = 'mostest recent tag' ) GROUP BY post_id ORDER BY created_at DESC LIMIT 5 OFFSET 0

Taryn East Over a year ago

aha - well that error is telling you that it can't tell whether you are grouping by votes.post_id or tags.post_id So - you have to tell it :)

Peter Eisentraut Over a year ago

This still won't work in PostgreSQL. It will work in PostgreSQL 9.1 if posts.id is the primary key.

James Kennedy · Accepted Answer · 2011-01-22 01:53:07Z

1

I came up with a variation which involves creating the table name join fiends dynamically rather than at compile time

model_columns = Model.column_names.collect do |c| "#{Model.table_name}.#{c}" end.join(",")

answered Jan 22, 2011 at 1:53

James Kennedy

213 bronze badges

Comments

Nick Urban · Accepted Answer · 2011-01-03 08:50:37Z

0

I was just dealing with this issue on Rails 3. Here's the solution I came up with:

group_models = [Post, Vote, etc.]
group_columns = ['"votes"."post_id"'] # whatever column you were specifying with :group 
group_models.each do |model|
  group_columns += model.column_names.map { |col| "\"#{model.table_name}\".\"#{col.name}\"" }
end
Post.where(whatever...).group(group_columns)

answered Jan 3, 2011 at 8:50

Nick Urban

3,5882 gold badges24 silver badges36 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related