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?

Answer 1

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.