rails: Uniq has_many :through with default_scope order does not work with PostgreSQL

Given these models:

class Tracker < ActiveRecord::Base
  has_many :issues
  has_many :categories, :through => :issues, :uniq => true
end

class Issue < ActiveRecord::Base
  belongs_to :tracker
  belongs_to :category
  default_scope order(:number)
end

class Category < ActiveRecord::Base
  has_many :issues
  default_scope order(:name)
end

When calling tracker.categories, on PostgreSQL, this fails with the following error:

ActiveRecord::StatementInvalid: PG::Error: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

SELECT DISTINCT "categories".* FROM "categories" INNER JOIN "issues" ON "categories"."id" = "issue"."category_id" WHERE "issue"."tracker_id" = 42 ORDER BY number, name

About this issue

  • Original URL
  • State: closed
  • Created 12 years ago
  • Comments: 22 (11 by maintainers)

Commits related to this issue

Most upvoted comments

The combination of order + distinct is what is causing the issue. The problem here is that PostgreSQL enforce you do add all columns you added in the ORDER BY clause in the SELECT list when you are using any aggregate functions

The way to get the valid SQL is explicitly adding the column in the select list.

I’m closing this one since we can’t do anything.