Rails has_many sorting of a column based on latest created

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.

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

My query which is not producing desired output:

Customer.joins('LEFT JOIN memberships')
      .where("memberships.cancel_at = (SELECT MAX(memberships.cancel_at) FROM memberships WHERE customers.id = memberships.customer_id)")
      .group('customers.id')
      .order('MAX(memberships.created_at) ASC')

CodePudding user response：

One very efficient way to solve this is with a lateral join:

SELECT c.*, latest_membership.cancelled_at AS cancelled_at 
FROM customers c
LEFT JOIN LATERAL (
  SELECT cancelled_at
  FROM memberships m
  WHERE m.customer_id = c.id -- lateral reference
  ORDER BY m.created_at
  LIMIT 1
) AS latest_membership ON TRUE
ORDER BY latest_membership.cancelled_at ASC NULLS LAST

This fancy new toy can be a bit hard to wrap your head around but its basically like a foreach loop - except in SQL. PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter. Crazy stuff!

ActiveRecord does not support lateral joins "out of the box" since its a fairly Postgres specific feature and it aims at being polyglot. But you can create them with either SQL strings or Arel.

Customer
  .select(
     'customers.*', 
     'latest_membership.cancelled_at AS cancelled_at'
  )
  .joins(<<~SQL)
    LEFT JOIN LATERAL (
      SELECT cancelled_at
      FROM memberships m
      WHERE m.customer_id = c.id -- lateral reference
      ORDER created_at
      LIMIT 1
    ) AS latest_membership ON TRUE
  SQL
  .order('latest_membership.cancelled_at ASC NULLS LAST')