MySQL to PostreSQL and Named Scope

Question

I've got a named scope for one of my models that works fine. The code is:

named_scope :inbox_threads, lambda { |user|
{
  :include => [:deletion_flags, :recipiences],
  :conditions => ["recipiences.user_id = ? AND deletion_flags.user_id IS NULL", user.id],
  :group => "msg_threads.id"
}}

This works fine on my local copy of the app with a MySQL database, but when I push my app to Heroku (which only uses PostgreSQL), I get the following error:

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

SELECT "msg_threads"."id" AS t0_r0, "msg_threads"."subject" AS t0_r1, "msg_threads"."originator_id" AS t0_r2, "msg_thr eads"."created_at" AS t0_r3, "msg_threads"."updated_at" AS t0_r4, "msg_threads"."url_key" AS t0_r5, "deletion_flags"."id" AS t1_r0, "deletion_flags"."user_id" AS t1_r1, "deletion_flags"."msg_thread_id" AS t1_r2, "deletion_flags"."confirmed" AS t1_r3, "deletion_flags"."created_at" AS t1_r4, "deletion_flags"."updated_at" AS t1_r5, "recipiences"."id" AS t2_r0, "recipiences"."user_id" AS t2_r1, "recipiences"."msg_thread_id" AS t2_r2, "recipiences"."created_at" AS t2_r3, "recipien ces"."updated_at" AS t2_r4 FROM "msg_threads" LEFT OUTER JOIN "deletion_flags" ON deletion_flags.msg_thread_id = msg_threads.id LEFT OUTER JOIN "recipiences" ON recipiences.msg_thread_id = msg_threads.id WHERE (recipiences.user_id = 1 AND deletion_flags.user_id IS NULL) GROUP BY msg_threads.id)

I'm not as familiar with the working of Postgres, so what would I need to add here to get this working? Thanks!

Answer 1

... must appear in the GROUP BY clause ...

MySQL flagrantly violates the standard in favor of simplicity and allows you to not specify every single column you're looking for in GROUP BY, instead relying on ordering to pick the "best" available value.

Postgres (and most other RDBMSes), on the other hand, requires that you name every single column that you're selecting in the GROUP BY clause. If you've manually assembled that query, you're going to need to, one by one, add each column into GROUP BY. If an ORM generated that query (which is what it looks like based on all the double quotes), you're going to need to check up on their Postgres support and/or file a bug with them.