php - MySQL 中的消息表，获取每个线程的最后一条消息

Question

我试图从每个对话中获取最后一条消息，唯一的问题是我没有使用对话表。

我正在使用一个消息表，我的表列如下所示:

id  from_id     to_id   text    isread  isseen  created_at  updated_at 

现在我可以像这样检索对话:

$messages = Message::select('*')->
                from(DB::raw("(select * from `messages` where `to_id` = ".Auth::id()." order by `created_at` desc) as sub"))->
                groupBy('from_id')->orderBy('created_at', 'desc')->
                paginate(7);

唯一的缺点是我没有检索每个对话的最后一条消息，我正在检索收到的最后一条消息。

如何检索每个对话的最后一条消息？

示例检索 user63 对话和每个对话的最后一条消息:

id  from_id     to_id   text    isread  isseen  created_at  updated_at 
23  224         63      a        0         0    2015-03-28 22:23:54     2015-03-28 22:23:54
20  63          225     b        0        0     2015-03-28 22:23:06     2015-03-28 22:23:06
16  225         63      hi       0        1     2015-03-28 22:21:32     2015-03-28 22:21:32

我想到了一个主意，但不确定:

$messages = Message::select('*')->
            from(DB::raw("
(
(select *, to_id as theuser from `messages` where `to_id` = ".Auth::id()." order by `created_at` desc)
union
(select *, from_id as theuser from `messages` where `from_id` = ".Auth::id()." order by `created_at` desc) group by theuser
) as sub"))->
            groupBy('from_id')->orderBy('created_at', 'desc')->
            paginate(7);

Answer 1

这也一定有效:这种方法的不同之处在于在 max() 函数的帮助下进行自连接，如下所示:

SELECT from_id, MAX(created_at) AS last FROM messages GROUP BY from_id

所以:

$pagedMessages = Message::select('*')->
  from(
    DB::raw("
        messages AS m 
        JOIN (
            SELECT from_id, MAX(created_at) AS lm FROM messages GROUP BY from_id
        ) AS lm 
        ON lm.from_id = m.from_id AND lm.created_at = m.created_at 
    "))->
paginate(7);