PostgreSQL, Rails and :order => problem

Question

I have the following line in my ActiveRecord model:

class Record < ActiveRecord::Base
    has_many :users, :through => :record_users, :uniq => true, :order => "record_users.index ASC"

This is intended to enable me to read out record.users in a way that I order using an index field in the record_users model.

The problem is that this fails on PostgreSQL with the following error:

ActionView::TemplateError (PGError: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Is there a way to fix the statement to make it work?

Can you post the generated SQL?

Luke Francl
– Luke Francl

2009-11-11 17:30:49 +00:00
Commented Nov 11, 2009 at 17:30 — Luke Francl
– Luke Francl, Commented Nov 11, 2009 at 17:30

Ben Scheirman · Accepted Answer · 2010-03-10 04:30:11Z

10

I suppose you could call it a bug in ActiveRecord. PosgreSQL is a bit more restrictive than MySQL. You can help out ActiveRecord by setting up the association like this instead:

class Record < ActiveRecord::Base
  has_many :users,
   :through => :record_users,
   :select => 'DISTINCT users.*, record_users.index',
   :order => "record_users.index ASC"

edited Mar 10, 2010 at 4:30

Ben Scheirman

41.1k21 gold badges104 silver badges139 bronze badges

answered Nov 11, 2009 at 18:21

Casper Fabricius

1,1799 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MkV Over a year ago

You can, since it is. MySQL is a bit more permissive and non-standard in what it accepts.

Casper Fabricius Over a year ago

I don't get it - what's wrong with my solution above? I'd like to know, because I'm using it in my own project running on Postgres ;)

cmaughan Over a year ago

Casper, I don't think james was saying it was a bad solution ;-) Also, is that a typo - DISTINCT?

cmaughan Over a year ago

Also, my question was - is there a way to fix the statement to make it work?. Your answer worked perfectly!

Casper Fabricius Over a year ago

Cool. I guess I assumed that James had voted my answer down and that his comment was the explanation for it - guess not ;)

|

Lance Carlson · Accepted Answer · 2011-05-11 16:19:12Z

2

Just posted this issue on rails's issue tracker on github (copied from lighthouse ticket so we could bring it back.. it was marked invalid):

https://github.com/rails/rails/issues/520

Promote it if you want this fixed nicely! :)

answered May 11, 2011 at 16:19

Lance Carlson

311 bronze badge

Comments

insane.dreamer · Accepted Answer · 2009-11-11 16:30:56Z

1

I ran into something similar before and I believe it's an AR bug related to PGSQL (https://rails.lighthouseapp.com/projects/8994/tickets/1711-has-many-through-association-with-order-causes-a-sql-error-with-postgresql).

I got around it by dropping the DISTINCT (:uniq) directive and resolved the uniq records another way. Kind of a bummer though.

answered Nov 11, 2009 at 16:30

insane.dreamer

2,0721 gold badge17 silver badges21 bronze badges

Collectives™ on Stack Overflow

PostgreSQL, Rails and :order => problem

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related