Sort a Rails model by an attribute on its polymorphic associations

active-record-query, polymorphic-associations, postgresql, ruby-on-rails

Solution

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.

Problem

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 } ```

Original source