将Eloquent模型的HasOne关联方法转换为MySQL查询语句

要实现Eloquent模型中currentPrice关联的逻辑，需要筛选出每个商品（item）对应的已发布（published_at小于当前时间）且最新的价格记录——优先取published_at最大的记录，若存在相同published_at的情况，则取id最大的那条。以下是补充完整的两种查询写法：

写法一：使用窗口函数（逻辑更直观，推荐） #

SELECT 
    items.id, 
    items.article_name, 
    prices.price, 
    prices.published_at, 
    weights.weight, 
    weights.amount
FROM items
INNER JOIN weights ON items.id = weights.item_id
INNER JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY item_id 
            ORDER BY published_at DESC, id DESC
        ) AS rn
    FROM prices
    WHERE published_at < NOW()
) AS prices ON items.id = prices.item_id AND prices.rn = 1
ORDER BY prices.published_at DESC;

写法二：使用分组子查询 #

SELECT 
    items.id, 
    items.article_name, 
    prices.price, 
    prices.published_at, 
    weights.weight, 
    weights.amount
FROM items
INNER JOIN weights ON items.id = weights.item_id
INNER JOIN (
    SELECT 
        item_id,
        MAX(id) AS price_id
    FROM prices
    WHERE published_at < NOW()
    GROUP BY item_id
    HAVING MAX(published_at) = (
        SELECT MAX(published_at) 
        FROM prices p2 
        WHERE p2.item_id = prices.item_id AND p2.published_at < NOW()
    )
) AS latest_price_ids ON items.id = latest_price_ids.item_id
INNER JOIN prices ON latest_price_ids.price_id = prices.id
ORDER BY prices.published_at DESC;

逻辑说明 #

• 两种写法都会先过滤掉未发布（published_at >= NOW()）的价格记录
• 窗口函数写法通过ROW_NUMBER()给每个商品的价格按published_at降序、id降序排序，取排名第一的记录，完全匹配Eloquent中ofMany的排序规则
• 分组子查询写法先锁定每个商品的最新发布时间，再在该时间范围内取最大的id，确保拿到唯一的最新价格记录

内容的提问来源于stack exchange，提问作者Michael Kocurek