按一对多关系排序

I have 2 tables:

我有2张桌子:

Message:
    id:         INT
    created_at: DATETIME

Comment:
    id:         INT
    message_id: INT
    created_at: DATETIME

One Message has many Comments.

一条消息有很多评论。

I want to get all Messages order by the most recent activity:

我希望通过最近的活动获取所有消息顺序:

If Message has Comments, then the most recent Comment's created_at is used as the Message activity indicator

如果Message有Comments,则最新的Comment的created_at用作Message活动指示符

If Message doesn't have Comments, it's created_at value is the activity indicator

如果Message没有Comments,则created_at值是活动指示符

So basically, I want to sort it like a classic e-mail or private messaging system.

所以基本上,我想把它分类为经典的电子邮件或私人消息系统。

Maybe i can INNER JOIN the Comments, but i don't think it's necessary to get all the Comments just for the sort.

也许我可以INNER加入评论,但我不认为有必要获得所有评论只是为了排序。

Also, I thought about creating a column in Message to save the last activity date and update it whenever a Comment is created, but I'd like to see if you have better solutions..

另外,我考虑在Message中创建一个列以保存最后一个活动日期,并在创建Comment时更新它,但我想看看你是否有更好的解决方案..

I'm using Doctrine, so if you have a Doctrine-based solution I'd rather that,

我正在使用Doctrine,所以如果你有一个基于Doctrine的解决方案,我宁愿这样做,

Thanks!

3 个解决方案

#1

What I would do in MySQL is something like:

我在MySQL中会做的是:

SELECT *
FROM Messages AS m
ORDER BY GREATEST(
   m.created_at, 
   (SELECT MAX( c.created_at ) FROM Comment AS c WHERE c.message_id = m.id )
) DESC

#2

Try something like this:

尝试这样的事情:

select m.message_id, c.created_at, m.created_at 
from message m
left join (
    select message_id, MAX(created_at) as created_at
    from comment
    group by message_id
) c on m.id = c.message_id
order by 
    ( case when c.created_at >= m.created_at then c.created_at
          else m.created_at
     end ) desc

The subquery will get most current comment date for each message, then message table will outer join the subquery because some message might not have any comments at all. Then order by greater value of comment created date and message created date.

子查询将获得每条消息的最新注释日期,然后消息表将外部加入子查询,因为某些消息可能根本没有任何注释。然后按评论创建日期和消息创建日期的更大值排序。

You might need to change the case syntax for mysql.

您可能需要更改mysql的大小写语法。

#3

If your comment table's id is an auto-incrment you can simply order by id desc and it will return the results in chronological order as long as you aren't doing deletes on the comment table which has a tendency to make inserts show up out of chronological order. Otherwise, you need to do something like:

如果您的评论表的id是自动加入,您可以简单地通过id desc进行排序,只要您没有在评论表上进行删除操作,它就会按时间顺序返回结果。按时间顺序排列。否则,您需要执行以下操作:

SELECT a.id, a.created_at 
FROM Message a, Comment b 
WHERE b.created_at > a.created_at 
ORDER BY b.created_at DESC

#1