Uniq has_many :through with default_scope order does not work with PostgreSQL #5701

Closed
codez opened this Issue Apr 2, 2012 · 21 comments

Projects

None yet
@codez
codez commented Apr 2, 2012

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
@oscardelben

Not necessarily related, but did you mean has_many :issues in the Category model?

Edit: You also don't show the relationship between Tracker and Comment here (you're calling tracker.comments but we don't know anything about it).

I've created a sample app though, and all calls to relationships ran fine for me (under sqlite3)

@codez
codez commented Apr 2, 2012

Uhm, sorry, my fault (too much editing of my example...). It should be tracker.categories of course, and has_many :issues.

I think the error is specific to PostgreSQL.

Might be related to #520

@masterkain

@codez, which version of Rails are you using?

@codez
codez commented Apr 3, 2012

rails 3.2.3, pg 0.13.2 on PostgreSQL 8.4.9

@thephw
thephw commented Apr 5, 2012

Ran into the same issue, currently thinking on what a good solution is. It's not necessarily particular to PostgreSQL, more like a malformed SQL statement. No issue in my deve environment with SQLite, but I'd assume (like a jerk) that is because SQLite isn't following strictly to standards. This article provided a decent rational of why it is required:

http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

@rubysolo
rubysolo commented Jun 8, 2012

I can confirm this issue with Rails 3.2.2 and PostgreSQL 9.1.3.

It's a bit janky, but there is a workaround ... simply add select: "{missing column}" to your has_many declaration. In the example above:

class Tracker < ActiveRecord::Base
  has_many :categories, through: :issues, uniq: true, select: "trackers.*, issues.number"
end

This adds the missing order by column to the select statement to keep Postgres happy.

@j0ni
j0ni commented Jan 23, 2013

I'm hitting this right now. @patrickwiseman, @codez, did you ever come up with a good solution?

@thephw
thephw commented Jan 23, 2013

Ended up using a named scope myself.

Best Regards,
Patrick Howard Wiseman

On Jan 23, 2013, at 5:38 PM, J Irving notifications@github.com wrote:

I'm hitting this right now. @patrickwisemanhttps://github.com/patrickwiseman,
@codez https://github.com/codez, did you ever come up with a good
solution?


Reply to this email directly or view it on
GitHubhttps://github.com/rails/rails/issues/5701#issuecomment-12626790.

@codez
codez commented Jan 24, 2013

Same here, I stopped using default_scope for ordering :(

@senny senny added a commit to senny/rails that referenced this issue Feb 8, 2013
@senny senny test-case to regressions on uniq hmt with default_scope order.
refs #5701
a0ec3c4
@senny
Member
senny commented Feb 8, 2013

I wrote a test-case to illustrate the issue on master: https://github.com/senny/rails/compare/5701_hmt_uniq_and_default_scope

I think there could be an additional problem on master as I had to prefix the table-name for the order call on the join-model. Otherwise It resulted in:

ActiveRecord::StatementInvalid: PG::Error: ERROR:  column posts.category_id does not exist
LINE 1: ...HERE "categorizations"."author_id" = $1  ORDER BY "posts"."c...
                                                             ^
: SELECT DISTINCT "posts".* FROM "posts" INNER JOIN "categorizations" ON "posts"."id" = "categorizations"."post_id" WHERE "categorizations"."author_id" = $1  ORDER BY "posts"."category_id" ASC, "posts"."title" ASC

/cc @jonleighton

@neerajdotname
Member

Looks like it is fixed in master. This is what I got

$ Tracker.new.categories

"SELECT DISTINCT \"categories\".* FROM \"categories\" INNER JOIN \"issues\" 
ON \"categories\".\"id\" = \"issues\".\"category_id\" 
WHERE \"issues\".\"tracker_id\" = $1  
ORDER BY \"issues\".number ASC, \"categories\".name ASC"
class Tracker < ActiveRecord::Base
  has_many :issues
  has_many :categories, -> { uniq }, :through => :issues
end

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

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

Am I missing something ?

@kulbida
kulbida commented Jul 29, 2013

I run into the same issue with rails 2.3.12 and pg 0.13.2. Did someone find a workaround?

@laurocaetano

I confirm what @neerajdotname posted before. I've created [this gist] 1 to reproduce this case and it seems to be fixed.

Update: I updated the gist and this issue seems to be present on master.

@rafaelfranca
Member

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.

@korobkov

Why do you think we can't do anything?
Why couldn't ActiveRecord just automatically add columns from ORDER BY clause to the SELECT columns list? (Possibly, for Postgresql adapter only)

@senny
Member
senny commented Dec 14, 2013

@korobkov this is adding more magic than we want. Active Record can't hide everything from the underlying database. It exposes it's functionality through a convenient API. You still need to be aware of the restrictions / features of your DB.

@korobkov

Well, I agree. This could be especially complicated in case of ordering on associated tables' columns…

@nhoffmann

Removing order would fix the problem here and is also not needed at all. I ran into the same problem, and was expecting something around these lines to work:

has_many :categories, -> { reorder('').uniq }, :through => :issues

But unfortunately it did not remove the order clause. At least not in the association context, using the code from the scope block runs fine in the console. Can anybody shine some light on why that is?

@brendon
brendon commented Feb 27, 2014

Was the fix to this problem in master ever back-ported to 3.2.x? It doesn't appear to have been.

I solved the problem by adding reorder('only the column from the table we're querying') to the end of my call to the :through relationship when using it in the application. It seems a bit hacky, but works fine.

@robin850
Member

@brendon : The 3-2-stable branch is not maintained anymore for bug fixes (only security ones) but thanks for the work-around. :-)

@korobkov korobkov added a commit to korobkov/fremantle that referenced this issue Feb 17, 2015
@korobkov korobkov Для основных моделей сортировку можно включить
(в связующих - нельзя из-за rails/rails#5701)
5039839
@korobkov korobkov added a commit to korobkov/fremantle that referenced this issue Apr 6, 2016
@korobkov korobkov Для основных моделей сортировку можно включить
(в связующих - нельзя из-за rails/rails#5701)
df037b2
@trostli
trostli commented Jun 28, 2016

I am having a ton of trouble getting this to work on 4.2.6. Has anyone had any luck with a workaround? The distinct works fine on its own or when order is unscoped but would love to also be able to order the records

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment