title: Rails has_many sorting of a column based on latest created_at record
body: <p>I have two models named as 'customer' and 'membership'. customer can have many memberships. I want to sort records based on membership's created_at and cancelled_at date columns. membership record should be a latest record created and cancelled should not include nil value. For example if a customer with id 1 has two membership records with created_at as 25 Dec 2020 and 27 Dec 2020 and cancelled dates as 1 jan 2021 and nil then this record should go to the bottom as latest membership is still active as its cancelled_at date is nil and should not come in asc or desc sorted list. But if a customer has 2 memberships with created_at as 25 Dec 2020 and 27 Dec 2020 and cancelled dates as 1 jan 2000 and 5 Jan 2000 then a record should popup with cancelled date as 5 Jan 2000.</p>
  <pre><code>Database Table - Customer
  Id      Name
  1        A
  2        B
  3        C
  4        D
  5        E
  
  Database Table - Membership
  id   customer_id   created_at    cancelled_at
  1       1           1 jan 2000     5 jan 2000
  2       1           2 jan 2000     nil
  3       2           1 Dec 1999     2 Dec 1999
  4       5           15 Jan 2000    16 Jan 2000
  5       5           17 Jan 2000    20 Jan 2000
  
  Then result should be (for asc order of cancelled_at)
  customer_id name cancelled at    
      2       B      2 Dec 1999
      5       E      20 Jan 2000
      1       A      nil
      3       C      nil
      4       D      nil
  </code></pre>
  <p>My query which is not producing desired output:</p>
  <pre><code>Customer.joins('LEFT JOIN memberships')
        .where(&quot;memberships.cancel_at = (SELECT MAX(memberships.cancel_at) FROM memberships WHERE customers.id = memberships.customer_id)&quot;)
        .group('customers.id')
        .order('MAX(memberships.created_at) ASC')
  </code></pre>