Postgres / Rails - Group by issues on Heroku deployment

Question

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

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
– John Beynon, Commented Apr 16, 2012 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
– Raoot, Commented Apr 16, 2012 at 10:57
you could always use mySQL on heroku addons.heroku.com/cleardb so you don't have to worry about Postgres issues. — John Beynon
– John Beynon, Commented Apr 16, 2012 at 11:28

maniek · Accepted Answer · 2012-04-16 11:38:31Z

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

answered Apr 16, 2012 at 11:38

maniek

7,3252 gold badges24 silver badges44 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:08:53Z

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.

edited May 23, 2017 at 10:08

CommunityBot

11 silver badge

answered Apr 16, 2012 at 11:38

John Beynon

37.5k8 gold badges92 silver badges97 bronze badges

Collectives™ on Stack Overflow

Postgres / Rails - Group by issues on Heroku deployment

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related