JOIN on Subquery Based on Date Evaluation

I have a query that includes a subquery that references one of the tables that my query joins on, but I also need to do an evaluation on the field returned from the subquery in my WHERE clause.

Here’s the current query (rough example) –

SELECT t1.first_name, t1.last_name, 
(SELECT created_at FROM customer_order_status_history WHERE order_id=t2.order_id AND order_status=t2.order_status ORDER BY created_at DESC LIMIT 1) AS order_date
FROM customers AS t1
INNER JOIN customer_orders as t2 on t2.customer_id=t1.customer_id

My subquery is currently returning the latest date from the customer_order_status_history table, but in my query I want to do an evaluation on the subquery in the WHERE clause such that I only want it if the the most recent created_at date is greater than a specific date condition (i.e. system date – 5 days). So in a way this is a conditional join on the customer_orders and customer_order_status_history tables where the final result should only be returned if the most recent record in customer_order_status_history (sorted by created_at in descending order) is greater than system date – 5 days.

Apologies in advance for the bad explanation but hopefully it is clear what I am trying to achieve here. Also I did not come up with this database schema and given the project constraints, I can not alter the schema.

Thanks!

Answer

Use a lateral join:

SELECT c.first_name, c.last_name, cosh.created_at
FROM customers c INNER JOIN
     customer_orders co
     ON co.customer_id = c.customer_id CROSS JOIN LATERAL
     (SELECT cosh.*
      FROM customer_order_status_history cosh
      WHERE cosh.order_id = co.order_id AND
            cosh.order_status = co.order_status AND
            cosh.created_at > now() - INTERVAL '5 DAY'
      ORDER BY cosh.created_at DESC
      LIMIT 1
     ) cosh