获取每组中的最后一行？

Hello basically im trying to copy the messaging system that facebook has onto my site.

你好基本上我试图将facebook上的消息系统复制到我的网站上。

This is the logic... "When a user1 CREATES A NEW MESSAGE to send to user7, A new thread is created with thread_id of 1(table: messages_thread) and a new entry is inserted into table:messages which is message_id 1(table:messages). When user7 REPLYS to user1's message, message2 is created, and it has a thread_id of 1.

这是逻辑......“当user1创建一个新的消息发送给user7时,创建一个新线程,其thread_id为1(table:messages_thread),并在表中插入一个新条目:message为message_id 1的消息(表:messages)。当user7 REPLYS到user1的消息时,创建了message2,它的thread_id为1。

Now when user 1 CREATES A NEW MESSAGE to sent to user7 thread 2 is created, and message 3 is created. When user7 replies to thread2, message 4 is created (hopefully you get the logic.)

现在,当用户1创建发送给用户7的新消息时,创建了线程2,并创建了消息3。当user7回复thread2时,会创建消息4(希望你得到逻辑。)

Everything is fine. the only problem is i need to select the newest message in the thread but im having trouble with the sql,

一切安好。唯一的问题是我需要在线程中选择最新的消息但是我遇到了sql的问题,

This sql that I have as of right now...

这个我现在的sql ...

SELECT max(message_id) message_id, m.thread_id, m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10

Produces this...

+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
| message_id | thread_id | body                 | user_id | to_id | message_status | new | date       | id | displayname  | username | profile_img |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
|          6 |         2 | Really nice          |       1 |     7 | read           |   0 | 1298617367 |  1 | Kenny  Blake | imkenee  | 28_1        |
|          4 |         1 | Whats good with you? |       1 |     7 | read           |   0 | 1298607438 |  1 | Kenny  Blake | imkenee  | 28_1        |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+

This is good but one small problem, it selects the first row in each group and im trying to select the newest (the last row) in each group

这是好的,但是一个小问题,它选择每个组中的第一行,并尝试选择每组中的最新(最后一行)

how can i do this? here is the tables. Thanks!

我怎样才能做到这一点?这是表格。谢谢!

Table: Messages_thread

+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
| id | user_id | subject        | from_status | to_status | from_s_delete | to_s_delete | date       |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
|  1 |       1 | Hey Kenny      | unread      | unread    |             0 |           0 | 1298607438 |
|  2 |       7 | Check out this | unread      | unread    |             0 |           0 | 1298617344 |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+

Table Messages

+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
| message_id | thread_id | user_id | to_id | body                                                      | message_status | is_sent_deleted | new | date       |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
|          1 |         1 |       1 |     7 | Whats good with you?                                      | read           |               0 |   0 | 1298607438 |
|          2 |         1 |       7 |     1 | Nothing Kenny just chilling. Whats up with you though???? | read           |               0 |   0 | 1298607473 |
|          4 |         1 |       1 |     7 | Just posted victor how are you man?                       | read           |               0 |   0 | 1298607956 |
|          5 |         2 |       7 |     1 | Look at this poem....                                     | read           |               0 |   0 | 1298617344 |
|          6 |         2 |       1 |     7 | Really nice                                               | read           |               0 |   0 | 1298617367 |
|          7 |         2 |       7 |     1 | Yea i know right :)                                       | unread         |               0 |   0 | 1298617383 |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+

2 个解决方案

#1

Group the threads in a subquery, which will return the last message for each thread:

将子线程中的线程分组,该子查询将返回每个线程的最后一条消息:

SELECT m.message_id, m.thread_id, m.body, m.user_id,
    m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m 
  INNER JOIN users u ON u.id = m.user_id
  INNER JOIN (
    SELECT MAX(message_id) MaxMsgIDForThread
    FROM messages
    WHERE to_id = 7
      AND (message_status = 'unread'
        or message_status='read'
        or message_status='saved') 
    GROUP BY thread_id
  ) g ON m.message_id = g.MaxMsgIDForThread
Order by m.message_id Desc
LIMIT 10

The WHERE may need to be moved to the outer query, right now it will pick the last message the meets the criteria, move it to the outer query if you want to skip the thread entirely if the conditions are not met.

WHERE可能需要移动到外部查询,现在它将选择符合条件的最后一条消息,如果要在不满足条件时完全跳过线程,则将其移动到外部查询。

You should also consider storing the message status as a ENUM which will help the comparisons.

您还应该考虑将消息状态存储为ENUM,这将有助于比较。

#2

-1

There is no way to add order to group by
But maybe this works:

没有办法将订单添加到分组但是也许这有效:

SELECT max(message_id) message_id, MAX(m.thread_id), m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10

#1