Sort a Rails model by an attribute on its polymorphic associations

Question

I imagine this is a duplicate question but I haven't found it yet so I figured I'd ask. I have a User model with different types of Profile models through a polymorphic association. Something like:

class User < ActiveRecord::Base
  belongs_to :profile, polymorphic: true
end

class Profile < ActiveRecord::Base
  has_one :user, as: :profile
end

class FacebookProfile < ActiveRecord::Base
  has_one :user, as: :profile
end

I'd like to perform a query on User that returns Users sorted by the first name of their profile object. Before when there wasn't any polymorphic relationship I could do this by starting with a joins on :profile, but I know that won't work with polymorphism.

Is there a better way to do this besides using sort or sort_by like:

User.all.sort_by {|user| user.profile.first_name }

Answer 1

It's doable with SQL.

SELECT users.*, COALESCE(profiles.first_name, facebook_profiles.first_name) AS c_first_name FROM users 
LEFT JOIN profiles ON users.profile_id=profiles.id AND users.profile_type="Profile" 
LEFT JOIN facebook_profiles ON users.profile_id=facebook_profiles.id AND users.profile_type="FacebookProfile" 
ORDER BY c_first_name ASC

You should be able to create a scope with this "manual" join on the User model, in that you can use COALESCE on the two columns to create an alias that you can sort on.

Try something like this

class User < ActiveRecord::Base
  belongs_to :profile, polymorphic: true

  scope :sort_by_first_name, -> { select("users.*", "COALESCE(profiles.first_name, facebook_profiles.first_name) AS c_first_name").joins("LEFT JOIN profiles ON users.profile_id=profiles.id AND users.profile_type='Profile'").joins("LEFT JOIN facebook_profiles ON users.profile_id=facebook_profiles.id AND users.profile_type='FacebookProfile'").order("c_first_name ASC") }
end

I haven't tried this myself but in theory it should work.