显示用户从邮件表，组最新消息

本文介绍了显示用户从邮件表，组最新消息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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 详细信息： 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.