CODEWITHSUNDEEP
ruby-on-rails-3postgresqlheroku
Raoot
Raoot

Reputation: 1771

Postgres / Rails - Group by issues on Heroku deployment

I'm developing on MySQL and have deployed to Heroku which is using Postgres. It seems some of my queries will need rewriting to work with Postgres. Can anyone assist with fixing my group by issues such as the below please?

Heroku Log error:

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

From my controller:

def itunes_top_tracks
 @itunes_top_tracks = ItunesData.all(:select => "artist_show, title, label_studio_network, isrc, SUM(units) as unitsum", :group => :isrc, :order => "unitsum DESC", :limit => 10)
end

I understand what the problem is and know how i'd do this in straight SQL, i'm just not sure of the Rails way?

Thanks all

Upvotes: 0

Views: 777

Answers (2)

maniek
maniek

Reputation: 7307

def itunes_top_tracks
 @itunes_top_tracks = ItunesData.all(:select => "artist_show, title, label_studio_network, isrc, SUM(units) as unitsum", 
    :group => [:artist_show, :title, :label_studio_network, :isrc], :order => "unitsum DESC", :limit => 10)
end

Upvotes: 1

John Beynon
John Beynon

Reputation: 37507

Take a look at this question - PostgreSQL - GROUP BY clause or be used in an aggregate function it provides direction in the answers for using group bys in Postgres.

Or consider using on the of the mySQL addons for Heroku and don't worry about trying to use Postgres.

Upvotes: 0

Related Questions