MySQL:ORDER BY 使用自己的属性或最新关联对象的属性

Question

对于我遇到的问题，以下数据库模型是相关的:商店有零个或多个评级。评级有零个或多个消息。这意味着评级具有外键 store_id，消息具有外键 rating_id。

我们用来查询所有评分，首先按其状态(已读与未读)排序，然后按其创建的时间戳排序:

// currenty query with simple order by
SELECT *
FROM rating rating
WHERE rating.store_id = $storeId
ORDER BY status DESC, created_timestamp DESC;

现在我们要考虑评级信息。 消息的评级应根据最新消息的created_timestamp以及该消息的状态(已读与未读)进行排序。我想在单个查询中选择所有评级(带消息和不带消息)。

对我来说，甚至很难将问题抽象为众所周知的问题集。有人能指出我正确的方向吗？

<小时/>

更新

在 Barmar 的帮助下，我创建了以下查询:

SELECT *
FROM rating r
LEFT JOIN (SELECT m.rating_id, m.created_timestamp, m.status
           FROM messages m
           JOIN (SELECT rating_id, MAX(created_timestamp) maxtime
                 FROM messages
                 GROUP BY rating_id) mmax
           ON m.rating_id = mmax.rating_id AND m.created_timestamp = mmax.maxtime) m
ON m.rating_id = r.id
ORDER BY m.status DESC, m.created_timestamp DESC

但是我对该解决方案有两个问题:

1. 它不处理评级没有关联消息的情况(在这种情况下，我希望根据评级本身的状态和created_timestamp对其进行排序)
2. 我不明白为什么我需要进行两次连接才能获取每条消息的最新评级

Answer 1

SELECT *
FROM rating r
LEFT JOIN (SELECT m.store_id, m.created_timestamp, m.status
           FROM messages m
           JOIN (SELECT store_id, MAX(created_timestamp) maxtime
                 FROM messages
                 GROUP BY store_id) mmax
           ON m.store_id = mmax.store_id AND m.created_timestamp = mmax.maxtime) m
ON m.store_id = r.store_id
ORDER BY m.status DESC, m.created_timestamp DESC