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.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With