Find most recent record from "has one through" relationship

Question

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

Answer 1

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.

Answer 2

You didn't stated any DBMS so, i would use subquery which is ANSI SQL.

select p.*, mv.*
from matrix_value_products mvp inner join 
     products p
     on p.id = mvp.product_id inner join
     matrix_values mv
     on mv.id = mvp.matrix_value_id
where mv.id = (select max(mvp1.matrix_value_id)
               from matrix_value_products mvp1
               where mvp1.product_id = mvp.product_id
               );