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
- test-case to regressions on uniq hmt with default_scope order. refs #5701 — committed to senny/rails by senny 11 years ago
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.