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')