Trying to order the results of includes in activerecord

Question

I've got an activerecord model that is joined to another model multiple times:

class Claim < ActiveRecord::Base
  belongs_to :user
  belongs_to :treated_by, foreign_key: :treated_by_id, class_name: 'User'
  belongs_to :person_involved, foreign_key: :person_involved_id, class_name: 'User'
  belongs_to :case_manager, foreign_key: :case_manager_id, class_name: 'User'
end

At some points I wish to grab the Claims and all their User instances so I avoid N+1 queries by using includes:

claims = Claim.all.includes(:user, :treated_by, :person_involved)

That goes great but now I want to sort the claims (without them being in memory) so I would like to do something like:

Claim.all.includes(:user, :person_involved, :treated_by).order('treated_by.last_name')

But I get invalid SQL unless I user the table name like this:

Claim.all.includes(:user, :person_involved, :treated_by).order('users.last_name')

Which clearly won't help me order by the treated by subset of users last_name for instance. How do I do this correctly?

Answer 1

As has been said by other people here order('treated_by.last_name') doesn´t work because treated_by is an alias working only in rails but is not included in the SQL statement to execute. order('users.last_name') does not fail but is not the result expected, you need the alias asigned automatically by rails which could be... I don't know, ¿t1?

By the way, that is not the "Rails way" to do it. When you add a belongs_to relation to your model you can add a scope when you can specify restriction for the ON statement every time you join throught this relation and how to sort by its columns: https://apidock.com/rails/ActiveRecord/Associations/ClassMethods/belongs_to

class Claim < ActiveRecord::Base
  belongs_to :user
  belongs_to :treated_by, foreign_key: :treated_by_id, class_name: 'User'
  belongs_to :person_involved, foreign_key: :person_involved_id, class_name: 'User'
  belongs_to :case_manager, foreign_key: :case_manager_id, class_name: 'User'

  belongs_to :treated_and_sort_by, -> { order last_name: :asc }, foreign_key: :treated_by_id, class_name: 'User'
end

So this should work like this...

Claim.all.includes(:user, :person_involved, :treated_and_sort_by)

Answer 2

As Abishek Kumar mentioned in his answer, you should probably use joins instead of includes, since includes is only there to prevent the N+1 query problem. It does seem to allow you to sort by association as well, but it will not help you if your associations have custom names.

I think you will have to step a little bit outside of ActiveRecord's nice and cozy place for a while, and write the join statement manually:

Claim.all
  .joins("INNER JOIN users AS treated_bys ON claims.treated_by_id = treated_bys.id")
  .order("treated_bys.last_name ASC")

...then you can spinkle the query with includes statements as well, if you need that to avoid N+1 queries, etc.