Search code examples
sqlruby-on-railsactiverecord

How To Order ActiveRecord Relation Based on Column Of Last Record Of Associated Object

I have 2 models as below:

Client
has_many :cases

Case
belongs_to :client

Let say I have 2 clients c1 & c2.
c1 has 1 case, and c2 has 2 cases.

c1    = Client.create(name: 'C1')
c2    = Client.create(name: 'C2')
case1 = Case.create(type: 'Normal', client: c1)
case2 = Case.create(type: 'Normal', client: c2)
case3 = Case.create(type: 'High', client: c2)

I want to get all clients and order by last case's type of each client asc/desc. So if I order by asc, I should have the results of 'c1' then c2. And if I order by desc, I should have the results of 'c2' then 'c1'. The following is what I've tried so far.

Client.includes(:cases).order('cases.type asc') # => 'C2,C1'
Client.includes(:cases).order('cases.type desc') # => 'C2,C1'

Since the c2 also has a case in type 'Normal', I think it seems like the query is finding client first, then it do something like client.cases. Then, I think it orders only cases under each client, and it does not order cases cross client.
I think my explanation is a little bit confusing. Please leave me a question if you have. Thanks.

Solution

  • I have changed the column 'case_type' instead of 'type' because we can not use column type in our table.

    To sort the data by case type of last record of client cases please try this.

    For ASC 

    Client.includes(:cases).sort_by{|m| m.cases.last.case_type}

    For DESC

     Client.includes(:cases).sort_by{|m| m.cases.last.case_type}.reverse