CODEWITHSUNDEEP
mysqlruby-on-railsdatabasepostgresqlruby-on-rails-4
user3188544
user3188544

Reputation: 1631

Switched MySQL to Postgres: "column must appear in the GROUP BY clause or be used in an aggregate function"

Switching a rails app from MySQL to Postgres gives the following error:

ERROR: column "contacts.id" must appear in the GROUP BY clause or be used in an aggregate function

Here is the scope in question:

class User < MyModel
  def self.top_contacts(timeframe = 1.week.ago, limit = 5)
    Contact.unscoped
          .where('created_at between ? and ?', timeframe, Time.now)
          .group(:user_id)
          .order('sum(score) DESC')
          .limit(limit)
          .includes(:user)
          .collect{|x| x.user}
  end
end
  1. How to fix this?
  2. Isn't using Rails as the database abstraction layer ensure switching the database should work seamlessly?

Upvotes: 2

Views: 970

Answers (1)

peterh
peterh

Reputation: 1

The problem is on the level of the SQL, which is invisible from your ORM layer. The problem is exactly with the RoR ORM, because it seems to generate a MySQL-friendly query which uses an extraordinary feature of the MySQL, which postgresql don't have.

The quick solution: give contacts.id to the columns by which you are GROUP-ing as well:

.group("user_id, contacts.id");

Upvotes: 2

Related Questions