Scope with order in Rails 3.2.11 on has_one association

Question

In my Rails 3.2.11 app I have a scope that I'm trying to order by based on an associated attribute. In my case I have a User model and a Profile model. The User has_one Profile, and my scope is on an attribute on the profiles table. Here's the scope:

In User.rb:

def self.with_default_show
  joins(:profile).where("profiles.show_all = true")
end

However the trouble I run into is in trying to declare order on that. For example, running:

joins(:profile).where("profiles.show_all = true").order("profiles.first_name DESC")

Gives me an error:

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

I know I can do .order("2") but that calls the second column in my Users table, not my Profiles table. How do I properly set the order on this scope to be by profiles.first_name?

sjain · Answer 1 · 2013-01-21T15:29:43.820

2

The ORDER BY clause can only be applied after the DISTINCT has been applied.

Also you must explicitly select for the clause you are ordering by.

User.select('profiles.*, profiles.first_name')
       .joins(:profile)
       .where("profiles.show_all = true")
       .order("profiles.first_name DESC")

As shown above, in order for your query to return the Profile attributes, you must explicitly select them also.

edited Jan 21 '13 at 15:29

answered Jan 21 '13 at 15:11

sjain

23,126
28
107
185

Thanks. If I change my scope to that I get a "syntax error at or near "DESC". `SELECT DISTINCT profiles.*, profiles.first_name DESC FROM`. – tvalent2 Jan 21 '13 at 15:19
removed `desc` from select as it will come in `order by` ..See my updated post above. – sjain Jan 21 '13 at 15:31

score 0 · Accepted Answer · answered Jan 21 '13 at 18:29

What ended up working was a combination of the two answers above:

def self.with_default_show
  joins(:profile)
  .where(profiles: {show_all: true})
  .select('users.*, profiles.first_name')
  .order('profiles.first_name')
end

With the sorting working as I had hoped.

Scope with order in Rails 3.2.11 on has_one association

2 Answers2