0

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

Raoot
  • 1,751
  • 1
  • 25
  • 51
  • 2
    This is the exact reason why you should be using the same DB for development as for production. When you start using your own SQL you're going to run into problems like this. – John Beynon Apr 16 '12 at 10:55
  • Yep, learning the hard way!! I'm not even convinced / set on using Heroku....it just seems the simplest and quickest solution, apart from this! – Raoot Apr 16 '12 at 10:57
  • you could always use mySQL on heroku https://addons.heroku.com/cleardb so you don't have to worry about Postgres issues. – John Beynon Apr 16 '12 at 11:28

2 Answers2

1
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
maniek
  • 7,087
  • 2
  • 20
  • 43
0

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.

Community
  • 1
  • 1
John Beynon
  • 37,398
  • 8
  • 88
  • 97