Q:

Find most recent record from "has one through" relationship

I have the following tables:

matrices - a matrix name and some other metadata.
matrix_values - stores various data points of a 2D matrix as a JSON value column (Postgres).  Also the FK matrix_id for a belongs-to association.
products - a matrix_value may be joined to a product via join table matrix_value_products

matrix_values MAY be associated with only one product, but are not required to be; in fact, most aren't.  Which is why is used a join table instead of a belongs-to FK relationship to avoid having mostly NULL values in a FK column

ERD looks like

Each matrix has several matrix_values where either A) none of them are associated with a product, or B) all of them are associated with a product (possibly different products).
In scenario A, the multiple records are a history of the 2D matrix, where the most recently created record is the current representation.  This scenario is easy for me to find the most recent matrix_value.
How can I create a query for scenario B in order to find the most recent matrix_values record per product? For matrix 777 below, I want to return matrix_values records 2 (most recent for product P) and 4 (most recent for product Q).

A:

SELECT finalValues.* FROM
matrix_values AS finalValues
INNER JOIN matrix_value_products AS finalProducts 
ON finalProducts.matrix_value_id = finalValues.id
AND finalValues.created_at = (
    SELECT max(created_at) 
    FROM matrix_values 
    INNER JOIN matrix_value_products
    ON matrix_values.id = matrix_value_products.matrix_value_id
    WHERE matrix_value_products.product_id = finalProducts.product_id
)   
WHERE
        matrix_id = 777

In essence you join the two tables and restrict them to the Matrix values of the appropriate Matrix. Then you Limit the whole Thing by a subquery that only Returns the most recent (thus max as I take it that created_at is a datetime(2) field) entry. You have to use the same inner join as only so you can Limit it to the products associated with the Matrix values you want to finally get.

