Query updated records in Database

Currently all our tables have a created_at and updated_at timestamp, e.g.

Cart

| ID | created_at | updated_at | user_id |
|----|------------|------------|---------|
| 1  | 2020-06-15 | 2020-06-15 | 6       |
| 2  | 2020-06-16 | 2020-06-16 | 7       |


CartItem

| ID | created_at | updated_at | qty     | cart_id |
|----|------------|------------|---------|---------|
| 3  | 2020-06-15 | 2020-06-16 | 2       | 1       |
| 4  | 2020-06-16 | 2020-06-18 | 1       | 1       |
| 5  | 2020-06-16 | 2020-06-18 | 6       | 2       |

User

| ID | created_at | updated_at | name                      |
|----|------------|------------|---------------------------|
| 6  | 2020-05-01 | 2020-06-19 | Lance                     |
| 7  | 2020-05-01 | 2020-05-01 | Barry (from Eastenders)   |

The updated_at field is modified with every INSERT.

This allows us to query all Cart‘s which have been updated since a particular point in time with something like this:

SELECT * FROM Cart WHERE updated_at > 2020-06-15

However this would not capture updates to FK relationships such as CartItemss and Users.

Is there a nice way to handle this so that we get all Carts which have either directly or indirectly (via FK relationships) been updated?

Answer

You can use exists:

select c.*
from cart c
where c.updated_at > '2020-06-16' or
      exists (select 1 from cartitem ci where ci.cart_id = c.cart_id and ci.updated_at > '2020-06-16') or
      exists (select 1 from user u where u.user_id = c.user_id and u.updated_at > '2020-06-16') ;