CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Joshua
Joshua

Reputation: 805

How order items based on votes

I am trying to render a list of the top 10 photos ordered by their votes this is how I decided to do it:

@top_photos = Photo.all(:joins => :votes, :select => "photos.*, count(*) as vote_total",:group => "votes.photo_id", :order => "vote_total DESC" )#, :limit => 10)

but i am getting the following error from my heroku logs:

ActiveRecord::StatementInvalid (PGError: ERROR:  column "photos.id" must appear in the GROUP BY clause or be used in an aggregate function

Works find on localhost with sqlite but does not work at all with postgres on heroku. Looking for some insight on what this may be or just an alternative way to achieve the same thing. Cheers

Upvotes: 0

Views: 66

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

Most databases don't allow to select non-aggregated columns which are not included in GROUP BY statement, SQLite and MySQL. It's not really a place to talk about which one is better but the short answer is: Include every non-aggregated column in the group by clause (every column in photos table basicaly). You will also want to stop using * as this will break your query if a new column is added.

Upvotes: 1

Related Questions