MySQL中的Messages表，获取每个线程的最后一条消息 [英] Messages table in MySQL, getting last message for each thread

问题描述

我正在尝试从每次对话中获取最新消息，唯一的问题是我没有使用对话表.

I am trying to get the last message from each conversation, the only problem is I am not using a conversations table.

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

I am using one messages table, my table columns looks like this:

id  from_id     to_id   text    isread  isseen  created_at  updated_at 

现在，我能够像这样检索对话:

Right now I am able to retrieve conversations like this:

$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);

唯一的缺点是我不是在每次对话中都检索到最后一条消息，而是在检索收到的最后一条消息.

The only downside to this is I am not retrieving the last message for each conversation, I am retrieving the last message received.

如何检索每次对话的最后一条消息?

How can I retrieve the last message of each conversation?

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

Example Retrieve user63 conversations and last message for each conversation:

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

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

I thought of an idea, not sure though:

$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);

推荐答案

我自己找到了解决方案，谢谢大家！ :)

I found a solution myself, thanks everyone! :)

$messages = Message::select('*')->
from(DB::raw("
    ( 
      SELECT *,`from_id` as `theuser`
      FROM `messages` WHERE `to_id` = 63
      union
      SELECT *,`to_id` as `theuser`
      FROM `messages` WHERE `from_id` = 63
      order by `created_at` desc
    ) as `sub`"))->groupBy('theuser')->
                   orderBy('created_at', 'desc')->
                   paginate(7);

这篇关于MySQL中的Messages表，获取每个线程的最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！