有没有更简单的方法来实现这种风格的用户消息？ [英] Is there a simpler way to achieve this style of user messaging?

问题描述

我为用户创建了一个消息传递系统，它允许他们向另一个用户发送消息。

使用者的收件匣会列出使用者所有的​​会话群组，包括所有的会话群组

用户只能与另一个用户进行一次会话。

当用户点击其中一个对话时，他们会转到显示整个对话的页面，他们在顶部有最新的帖子。

我有两个表格：

• userconversation


• usermessages

userconversation

包含一个自动增量ID，即会话ID，以及userId和friendId。

启动第一个会话的用户将始终为userId和收件人friendId，则此对话将永远不会更改。

  + ---- + ------ + ---------- + 
 | id | userId | friendId | 
 + ---- + -------- + ---------- + 
  

usermessages

包含特定邮件以及读取标记，时间和会话ID

  + ---- + --------- + -------- + ---- -  + ------ + ---------------- + 
 | id |消息| userId |读|时间| conversationId | 
 + ---- + --------- + -------- + ------ + ------ + ------- --------- + 
  

工作原理 / p>

当用户转到消息另一个用户时，将运行查询以检查两个用户在用户转换表中是否有匹配项，如果是，则 conversationId ，并且会话继续进行，如果不是，则为它们创建具有唯一 conversationId 的新行。

它变得复杂

到目前为止一切都很好，但是当它显示所有的邮件收件箱

为了能够列出会话，您必须首先找到每个会话的最新帖子，但是因为你不能在一个组之前进行排序，所以不可能在两个表上进行一个查询，所以我必须使用以下内容：

  SELECT 
 c.id，
 c.userId，
 c.friendId，
 m2.message，
 m2.read，
 UNIX_TIMESTAMP（m2.time），
 user1.username，
 user2.username 
 FROM 
（SELECT MAX（m1.id）AS MessageID 
 FROM usermessages m1 
 GROUP BY m1.conversationId）latest_msg 
 
 INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id 
 INNER JOIN用户会话c ON m2.conversationId = c.id 
 INNER JOIN user user1 ON c.userId = user.id 
 INNER JOIN user user2 ON c.friendId = user.id 
 
 WHERE c.userId =：userId或c.friendId =：userId 
 ORDER BY m2.id DESC 
 LIMIT 10 
  

认为这是最好的方法，但是不能想到别人的方法呢？

数据库表是InnoDB，以加快连接和改进数据完整性，所以我不能有两个自动增量行。

有另一种方法，我可以摆脱userconversation表，并创建一个唯一的ID，放在conversationId列？我可以将userId和friendId移动到usermessages ...但这会创建很多冗余数据？

解决方案

可能我不能正确地理解你的问题...但对我来说，解决方案很简单：

  SELECT c。 ，MAX（m.time）as latest_post 
 FROM conversationations as c 
 INNER JOIN messages as m ON c.id = m.conversation_id 
 WHERE c.userId = 222 OR c.friendId = 222 
 GROUP BY c.id 
 ORDER BY latest_post DESC 
  

这里是我的测试数据：

对话：

  id userId friendId 
 1 222 333 
 2 222 444 
  

  id message time（Desc）conversation_id 
 14 rty 2012-05-14 19:59： 55 2 
 13 cvb 2012-05-14 19:59:51 1 
 12 dfg 2012-05-14 19:59:46 2 
 11 ert 2012-05-14 19:59 ：42 1 
 1 foo 2012-05-14 19:22:57 2 
 2 bar 2012-05-14 19:22:57 2 
 3 foo 2012-05-14 19： 14:13 1 
 8 wer 2012-05-13 19:59:37 2 
 9 sdf 2012-05-13 19:59:24 1 
 10 xcv 2012-05-11 19 ：59：32 2 
 4 bar 2012-05-10 19:58:06 1 
 6 zxc 2012-05-08 19:59:17 2 
 5 asd 2012-05-08 19:58:56 1 
 7 qwe 2012-05-04 19:59:20 1 
  

查询结果：

  id userId friendId latest_post 
 2 222 444 2012-05 -14 19:59:55 
 1 222 333 2012-05-14 19:59:51 
  

如果这不是它...只是忽略我的回答：P

希望这有助于

I have created a messaging system for users, it allows them to send a message to another user. If it is the first time they have spoken then a new conversation is initiated, if not the old conversation continues.

The users inbox lists all conversations the user has had with all other users, these are then ordered by the conversation which has the latest post in it.

A user can only have one conversation with another user.

When a user clicks one of these conversations they are taken to a page showing the whole conversation they've had with newest posts at the top. So it's kind of like a messaging chat functionality.

I have two tables:

• userconversation
• usermessages

userconversation

Contains an auto increment id which is the conversation id, along with the userId and the friendId.

Whoever initates the first conversation will always be userId and the recipient friendId, this will then never change for that conversation.

+----+--------+----------+
| id | userId | friendId |
+----+--------+----------+

usermessages

Contains the specific messages, along with a read flag, the time and conversationId

+----+---------+--------+------+------+----------------+
| id | message | userId | read | time | conversationId |
+----+---------+--------+------+------+----------------+

How it works

When a user goes to message another user, a query will run to check if both users have a match in the userconversation table, if so that conversationId is used and the conversation carries on, if not a new row is created for them with a unique conversationId.

Where it gets complicated

So far all is well, however when it comes to displaying the message inbox of all conversations, sorted on the latest post, it get's tricky to do with one query..

To be able to list the conversations you must first find the latest post of each conversation, but as you can't order by before a group this is impossible to do with one query on two tables, so I have to use the following:

SELECT  
    c.id,
    c.userId,
    c.friendId,
    m2.message,
    m2.read,
    UNIX_TIMESTAMP(m2.time),      
    user1.username,
    user2.username  
FROM 
    (SELECT MAX(m1.id) AS MessageID 
     FROM usermessages m1 
     GROUP BY m1.conversationId) latest_msg

INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id 
INNER JOIN userconversation c ON m2.conversationId = c.id
INNER JOIN user user1 ON c.userId = user.id
INNER JOIN user user2 ON c.friendId = user.id

WHERE c.userId = :userId OR c.friendId = :userId
ORDER BY m2.id DESC
LIMIT 10

I just don't think this is the best way it can be done but can't think of others ways too approach it?

The database table is InnoDB to speed up the joins and improve data integrity so I can't have two auto increment rows.

Is there another way I could get rid of the userconversation table and create a unique Id to put in the conversationId column? I could then just move the userId and friendId over to usermessages ... but this would create a lot of redundant data?

解决方案

hmm maybe i'm not understanding correctly your problem... but to me the solution is quite simple:

SELECT c.*, MAX(m.time) as latest_post 
FROM conversations as c 
INNER JOIN messages as m ON c.id = m.conversation_id
WHERE c.userId = 222 OR c.friendId = 222 
GROUP BY c.id
ORDER BY latest_post DESC

here's my test data:

Conversations :

id  userId  friendId
1   222     333
2   222     444

Messages :

id  message     time (Desc)     conversation_id
14  rty     2012-05-14 19:59:55     2
13  cvb     2012-05-14 19:59:51     1
12  dfg     2012-05-14 19:59:46     2
11  ert     2012-05-14 19:59:42     1
1   foo     2012-05-14 19:22:57     2
2   bar     2012-05-14 19:22:57     2
3   foo     2012-05-14 19:14:13     1
8   wer     2012-05-13 19:59:37     2
9   sdf     2012-05-13 19:59:24     1
10  xcv     2012-05-11 19:59:32     2
4   bar     2012-05-10 19:58:06     1
6   zxc     2012-05-08 19:59:17     2
5   asd     2012-05-08 19:58:56     1
7   qwe     2012-05-04 19:59:20     1

Query result :

id  userId  friendId    latest_post
2   222     444     2012-05-14 19:59:55
1   222     333     2012-05-14 19:59:51

If that's not it... just ignore my answer :P

Hope this helps

这篇关于有没有更简单的方法来实现这种风格的用户消息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！