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 allow me to read records. so that I order the use of the index field in the record_users model.

The problem is that this does not work in PostgreSQL with the following error:

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

Is there any way to fix the statement so that it works?

Answer 1

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

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

Answer 2

Just posted this issue on the rails issue tracker on github (copied from a beacon ticket so we can return it .. it was marked as invalid):

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

Promote it if you want it fixed beautifully! :)

Answer 3

I came across something similar before, and I believe this is an AR error 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 this by dropping the DISTINCT directive (: uniq) and resolving uniq entries in a different way. Kind of a wreck though.