显示用户从邮件表，组最新消息 [英] Display latest messages from messages table, group by user

问题描述

我试图创建一个收件箱中的用户之间的消息传递。
这里有如下表：

I'm trying to create an inbox for messaging between users.
Here are the following tables:

Messsages
Id | Message_from | message_to | message
1  | 2            |   1        | Hi
2  | 2            |   1        | How are you
3  | 1            |   3        | Hola
4  | 4            |   1        | Whats up
5  | 1            |   4        | Just Chilling
6  | 5            |   1        | Bonjour

Users
Id | Name
1  | Paul
2  | John
3  | Tim
4  | Rob
5  | Sarah
6  | Jeff

我想显示的收件箱显示用户该人已通报的名单和last_message无论从用户

I'd like to display an inbox showing the list of users that the person has communicated and the last_message from either users

保罗的收件箱中：

Name | user_id | last_message
Sarah| 5       | bonjour
Rob  | 4       | Just Chilling
Tim  | 3       | Hola
John | 2       | How are you 

我如何做到这一点与活动记录？

How do I do this with Active Records?

推荐答案

这应该是相当有效的：

SELECT u.name, sub.*
FROM  (
   SELECT DISTINCT ON (1)
          m.message_from AS user_id
        , m.message AS last_message
   FROM   users    u
   JOIN   messages m ON m.message_to = u.id
   WHERE  u.name = 'Paul'   -- must be unique
   ORDER  BY 1, m.id DESC
   ) sub
JOIN  users u ON sub.user_id = u.id;

计算的最新消息，所有用户在子查询子使用 DISTINCT ON 。然后加入到 表用户第二次来解析名称。

Compute all users with the latest message in the subquery sub using DISTINCT ON. Then join to table users a second time to resolve the name.

为 DISTINCT ON 详细信息：
<一href="http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564">Select在每一组中第一行按组？

Details for DISTINCT ON:
Select first row in each GROUP BY group?

除了：使用ID和名称列名是不是一个非常有用的命名约定

Aside: Using "id" and "name" as column names is not a very helpful naming convention.

这篇关于显示用户从邮件表，组最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！