Существует ли более простой способ достижения этого стиля обмена сообщениями пользователей?

Ответ 1

Хмм, может быть, я неправильно понимаю вашу проблему... но для меня решение довольно просто:

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

здесь мои тестовые данные:

Беседы:

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

Надеюсь, что это поможет

Ответ 2

Если вы запрашиваете способ сохранить все свои текущие функциональные и рабочие потоки, но сохраните данные в одной таблице, я думаю, что вы довольно близки.

Вместо того, чтобы conversationId быть ключом к другой таблице, я бы вместо этого указал на идентификатор сообщения, которое начало разговора. Это создало бы отношения между родителями и детьми между сообщениями, которые начали разговор, и всеми теми, которые последовали за ним. Чтобы иметь возможность видеть все разговоры, вы просто выбираете все сообщения, где conversationId имеет значение null. Ниже представлено сообщение из 2 сообщений:

+----+---------+------+------------------+----------------+--------+----------+
| id | message | read | time             | conversationId | toUser | fromUser |
+----+---------+------+------------------+----------------+--------+----------+
| 1  |  test 1 |  0   | (some timestamp) |  null          |  3     |   4      |
| 2  |  test 2 |  0   | (some timestamp) |   1            |  4     |   3      |
+----+---------+------+------------------+----------------+--------+----------+

Разговор был инициирован пользователем 3. Все сообщения в разговоре могут быть отфильтрованы на conversationId. Одно из ограничений этого дизайна заключается в том, что только различие между разговором может быть только у двух пользователей.

Обновить

Вы можете получить последнее сообщение с идентификатором беседы следующим образом:

SELECT id, message 
FROM userMessages 
WHERE conversationId = {conversationId} 
ORDER BY time DESC 
LIMIT 1

Ответ 3

Если у вас есть только один разговор между пользователями, я не вижу причины для отдельной таблицы цепочек. Чтобы этот запрос работал быстро, вам понадобится составной индекс на (user, message_id), который невозможен, если эти поля находятся в разных таблицах. Переместите user_id и friend_id в userconversations. Это сделает вашу таблицу 8 байтов на запись более тяжелой (даже если предполагается 8 -байтовые идентификаторы), что вряд ли является проблемой для таблицы, содержащей текстовые сообщения.

Если у вас мало разговоров на пользователя с большим количеством сообщений в каждом, используйте это:

SELECT  um.*
FROM    (
        (
        SELECT  MAX(id) AS messageId
        FROM    usermessages m1
        WHERE   user_id = :me
        GROUP BY
                friend_id
        ORDER BY
                messageId DESC
        LIMIT 10
        )
        UNION ALL
        (
        SELECT  MAX(id) AS messageId
        FROM    usermessages m1
        WHERE   frient_id = :me
        GROUP BY
                user_id
        ORDER BY
                messageId DESC
        LIMIT 10
        )
        ) q
JOIN    usermessages um
ON      um.id = q.messageId
ORDER BY
        id DESC
LIMIT 10

Создайте отдельные индексы на user_id и friend_id

Если у вас много разговоров с несколькими сообщениями в каждом, используйте этот запрос:

(
SELECT  *
FROM    usermessages um
WHERE   user_id = :me
        AND id = 
        (
        SELECT  MAX(id)
        FROM    usermessages umi
        WHERE   umi.user_id = um.user_id
                AND umi.friend_id = um.friend_id
        )
ORDER BY
        id DESC
LIMIT 10
)
UNION ALL
(
SELECT  *
FROM    usermessages um
WHERE   frient_id = :me
        AND id = 
        (
        SELECT  MAX(id)
        FROM    usermessages umi
        WHERE   umi.user_id = um.user_id
                AND umi.friend_id = um.friend_id
        )
ORDER BY
        id DESC
LIMIT 10
)
ORDER BY
        id DESC
LIMIT 10

Идея этого запроса заключается в том, что он просто сбрасывает все сообщения для данного пользователя, проверяя, что каждое сообщение является последним в его разговоре. Это может быть намного быстрее, чем сортировка всех последних сообщений для всех разговоров (если у вас их много).

Чтобы это быстро работало, создайте индексы на

friend_id
user_id, friend_id

Ответ 4

Если вы хотите упростить свой запрос, вы должны добавить последний идентификатор сообщения в свою таблицу userconversation:

ALTER TABLE userconversation ADD lastusermessageid

то каждый раз, когда вы добавляете новое сообщение, вы должны обновить свою дискуссию пользователя:

INSERT INTO userconversation(userId, friendId, lastusermessageid)
VALUES (:userId, :friendId, :lastusermessageid)
ON DUPLICATE KEY UPDATE lastusermessageid = VALUES(lastusermessageid)

и, наконец, добавить индексы для всех внешних ключей:

SELECT  
    c.id,
    c.userId,
    c.friendId,
    m.message,
    m.read,
    UNIX_TIMESTAMP(m.time),      
    user1.username,
    user2.username  
FROM 
    userconversation c
    INNER JOIN usermessages m ON c.lastusermessageid = m.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
    m.id DESC
LIMIT 10

Ответ 5

Поскольку у данной пары пользователей может быть не более одного разговора, нет необходимости "изобретать" отдельный ключ, чтобы идентифицировать разговоры. Кроме того, формулировка вашего вопроса, похоже, предполагает, что сообщение всегда отправляется одному пользователю, поэтому я, вероятно, займусь чем-то вроде этого:

Теперь о данной модели есть несколько вещей:

• Предполагается, что сообщения между теми же двумя пользователями не могут генерироваться чаще, чем разрешение, предоставляемое типом, используемым для SEND_TIME. ¹
• Направление сообщения не определяется по заказу USER1_ID и USER2_ID, но с отдельным флагом (DIRECTION). Таким образом, сообщение между заданными пользователями всегда будет иметь одинаковую комбинацию USER1_ID и USER2_ID (в соответствии с приведенным выше CHECK), независимо от того, кто отправил и кто получил сообщение. Это значительно упрощает запрос.
• Прискорбно, что все таблицы InnoDB кластеризуются, поэтому вторичный индекс I1 относительно дорого. Есть способы обойти это, но возникающие осложнения, вероятно, не стоят того.

В этой модели данных довольно легко сортировать "беседы" (идентифицированные парами пользователей) последним сообщением. Например (замените 1 на желаемый пользовательский USER_ID):

SELECT *
FROM (
    SELECT USER1_ID, USER2_ID, MAX(SEND_TIME) NEWEST
    FROM MESSAGE
    WHERE (USER1_ID = 1 OR USER2_ID = 1)
    GROUP BY USER1_ID, USER2_ID
) Q
ORDER BY NEWEST DESC;

(OR USER2_ID = 1 является причиной вторичного индекса I1.)

Если вы хотите не только последние времена, но и последние сообщения, вы можете сделать что-то вроде этого:

SELECT * FROM MESSAGE T1
WHERE
    (USER1_ID = 1 OR USER2_ID = 1)
    AND SEND_TIME = (
        SELECT MAX(SEND_TIME)
        FROM MESSAGE T2
        WHERE
            T1.USER1_ID = T2.USER1_ID
            AND T1.USER2_ID = T2.USER2_ID
    )
ORDER BY SEND_TIME DESC;

Вы можете играть с ним в SQL Fiddle.

¹ Если это не так, вы можете использовать монотонный инкрементирующий INT вместо этого, но вам придется SELECT MAX(...) самостоятельно, так как автоинкремент не работает с подмножеством PK; или просто сделать его одним PK и иметь вторичные индексы как для USER1_ID, так и для USER2_ID (к счастью, они будут более тонкими, поскольку ПК будет более тонким).

Ответ 6

Как создать быструю Facebook-подобную систему сообщений. проверены и широко используются пользователями Arutz Sheva - http://www.inn.co.il (иврит).

• создать таблицу "topic" (беседа):

    CREATE TABLE pb_topics (
    t_id int(11) NOT NULL AUTO_INCREMENT,
    t_last int(11) NOT NULL DEFAULT '0',
    t_user int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (t_id),
    KEY last (t_last)
  ) ENGINE=InnoDB AUTO_INCREMENT=137106342 DEFAULT CHARSET=utf8

• создать связь между пользователем и беседой:

      CREATE TABLE pb_links (
    l_id int(11) NOT NULL AUTO_INCREMENT,
    l_user int(11) NOT NULL DEFAULT '0',
    l_new int(11) NOT NULL DEFAULT '0',
    l_topic int(11) NOT NULL DEFAULT '0',
    l_visible int(11) NOT NULL DEFAULT '1',
    l_bcc int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (l_id) USING BTREE,
    UNIQUE KEY topic-user (l_topic,l_user),
    KEY user-topicnew (l_user,l_new,l_topic) USING BTREE,
    KEY user-topic (l_user,l_visible,l_topic) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=64750078 DEFAULT CHARSET=utf8

• создать сообщение

      CREATE TABLE pb_messages (
    m_id int(11) NOT NULL AUTO_INCREMENT,
    m_from int(11) NOT NULL,
    m_date datetime NOT NULL DEFAULT '1987-11-13 00:00:00',
    m_title varchar(75) NOT NULL,
    m_content mediumtext NOT NULL,
    m_topic int(11) NOT NULL,
    PRIMARY KEY (m_id),
    KEY date_topic (m_date,m_topic),
    KEY topic_date_from (m_topic,m_date,m_from)
  ) ENGINE=InnoDB 

Разговор может состоять из двух или более друзей (BCC был добавлен как электронная почта, но вы можете пропустить его).

Вставьте новое сообщение:  1. Создать новую тему  2. Создание ссылок для пользователей (от/до)  3. Добавить сообщение (4. Обновить таблицу кэша пользователей - у пользователя есть сообщения)

Добавить сообщение в тему:  Добавить сообщение

Выберите папку:

select 
     z.*, group_concat(u_name) as users_name from
         (select max(m_id) as m_id, m_topic as t_id,  m_From, m_title,m_date, l_new 
              from pb_links as l1, pb_messages 
              where l1.l_user=<user>  and m_from < If(inbox, "<>", "=") > and m_topic=l_topic and l1.l_visible=1 
               group by m_topic order by m_id desc limit " & iPage * 35 & ",35) z
           left join  pb_links l2  on (l2.l_topic=t_id)
           left join  users  on (l_user=u_id and l_bcc=0 and l_user<user>)  
            group by l_topic order by m_date desc;

Подробнее:

Первый - это внутренний выбор - это самый быстрый способ (я проверил около 7 других опций, также проверял в версиях Percona/MariaDB), чтобы получить все сообщения и получить также последнее сообщение для отображения в списке. Также смотрите во внутреннем IF - в папке "Входящие", последнее сообщение - это кто-то, но не я, а In outbox - наоборот. LIMIT используется для подкачки.

Внешний, используемый для добавления списка пользователей (просто имя имени запятой) и дополнительной информации для только одного сообщения для каждой темы и после подкачки (мне нужно добавить список пользователей только для 35-страничных сообщений, а не для всей моей большой истории).

Кроме того, я написал на иврите здесь: http://blogs.microsoft.co.il/blogs/moshel/archive/2010/08/12/quot-x-quot.aspx  для создания простой таблицы кеша и запретить рабочую нагрузку для подсчета количества из таблицы занятости.

Ответ 7

Почему вы разбиваете данные на разговоры?

Если бы это был я, я бы использовал одну таблицу под названием "usermessages" со следующим форматом:

+----+--------+----------+-------------+------------+--------+
| id | userto | userfrom | timecreated | timeviewed | message|
+----+--------+----------+-------------+------------+--------+

Разговор идентифицируется комбинацией столбцов "userto" и "user from". Итак, когда вы хотите выбрать весь разговор:

SELECT * FROM usermessages 
WHERE (userto = :userto OR userto = :userfrom) 
AND (userfrom = :userfrom OR userfrom = :userto) 
ORDER BY timecreated DESC 
LIMIT 10

Ответ 8

он используется на fiverr.com и www.infinitbin.com. Я разработал бесконечность. Он имеет две базы данных, такие как ваши. таблица входящих сообщений: -

+----+--------+----------+-------------+------------+--------------------------------+
| id | useridto | useridfrom | conversation | last_content | lastviewed | datecreated|
+----+--------+----------+-------------+------------+--------------------------------+

Эта таблица очень важна, используется для перечисления цепочек/входящих. Поле last_content содержит 140 символов из последнего сообщения между цепочками. lastviewed - целое поле, пользователь, который продолжает отправлять сообщение, является последним просмотренным, если другой пользователь в разговоре читает сообщение. он обновляется до NULL. Поэтому для получения уведомлений вы для lastviewed, который не является нулевым, а не зарегистрированным идентификатором пользователя.

Поле беседы является "userid-userid", поэтому строки. Чтобы проверить, начали ли пользователи разговор, вы объединяете там user_ids с дефисом и проверяете его.

Такая система обмена сообщениями очень усложняет ее.

Вторая таблица довольно проста.

+----+--------+----------+-------------+-------+
| id | inboxid | userid | content | datecreated|
+----+--------+----------+-------------+-------+

Ответ 9

Я не тестировал этот подход, так как сейчас у меня нет доступа к mysqldb. Но, я думаю, вы должны это сделать, используя функцию ранжирования. Поскольку mysql не имеет эквивалента функции row_number для Oracle, я думаю, вы можете сделать это следующим образом:

Select * from (
Select 
    uc.id, 
    uc.user_id, 
    uc.friend_id 
    um.message
    um.read, 
    um.time,
    @rownum := IF(@prev_val = um.conversation_id, @rownum + 1, 1) AS rank,
    @prev_val := um.conversation_id
From
    userconversation uc,
    usermessages um,
    (select @row_num:=1) rows,
    (select @prev_val:='') partitions
Where 
    uc.id=um.conversation_id        
    and c.userId = 222 OR c.friendId = 222 

Order By 
    um.conversation_id,um.id desc
)t where t.rank=1

Ответ 10

Я бы установил его таким образом

Данные таблицы

conversations (#id, last_message_id)

participation (#uid1, #uid2, conversation_id)

messages (#conversation_id, #id, uid, contents, read, *time)

разговоры

Эта таблица будет использоваться в основном для генерации нового идентификатора для каждой беседы вместе с вычисленным полем последнего обновления (для оптимизации). Эти два пользователя были отключены от этой таблицы и переведены в participation.

Участие

В этой таблице записаны разговоры между двумя пользователями в обоих направлениях; чтобы объяснить, почему, взгляните на следующий ключ:

ALTER TABLE `table` ADD PRIMARY(uid1, uid2);

Хотя это хорошо для обеспечения уникальности и простого поиска, вы должны знать следующее поведение:

• SELECT * FROM table WHERE uid1=1 AND uid2=2
• SELECT * FROM table WHERE uid1=1
• SELECT * FROM table WHERE uid1=1 AND uid2>5
• SELECT * FROM table WHERE uid2=2

Первые два запроса выполняются очень хорошо, MySQL также оптимизирует поиск идентичности в первой части вашего ключа. Третья также дает неплохую производительность, так как вторая часть вашего ключа может использоваться для запросов диапазона. Последний запрос не работает хорошо, потому что индекс "левый" и, следовательно, выполняет полное сканирование таблицы.

сообщения

В этой таблице хранятся фактические отправленные сообщения, содержащие идентификатор разговора, идентификатор отправителя, содержимое, флаг чтения и время его отправки.

Операция

отправка сообщений

Чтобы определить, был ли разговор между двумя пользователями уже установлен, вы можете просто запросить таблицу participation:

SELECT conversation_id FROM participation WHERE uid1=:sender_id AND uid2=:receiver_id

Если он еще не существует, вы создаете обе записи:

INSERT INTO conversations (last_message_id) VALUES (NULL);
# fetch last insert id here
INSERT INTO participation VALUES (:sender_id, :receiver_id, :conversation_id), (:receiver_id, :sender_id, :conversation_id);
INSERT INTO messages VALUES (:conversation_id, 0, :sender_id, :message_contents, 0, NOW());
UPDATE conversations SET last_message_id=LAST_INSERT_ID() WHERE id = :conversation_id

Если разговор уже настроен:   INSERT INTO messages VALUES (: convers_id, 0,: sender_id,: message_contents, 0, NOW());   Переговоры UPDATE SET last_message_id = LAST_INSERT_ID() WHERE id =: convers_id

Примечание. Оператор UPDATE можно планировать как LOW_PRIORITY, потому что вы не всегда должны быть на 100% правильнее.

обзор беседы

Это стало более простым запросом:

SELECT other_user.name, m.contents, m.read, c.id
FROM participation AS p
INNER JOIN user AS other_user ON other_user.id = p.uid2
INNER JOIN conversation AS c ON c.id = p.conversation_id
INNER JOIN messages AS m ON m.id = c.last_message_id
WHERE p.uid1 = :user_id
ORDER BY m.time DESC
LIMIT 50

Отказ от ответственности: я не проверял это, но запись должна иметь смысл для вас.

Оптимизация

Другая причина, по которой хорошо иметь двухстороннюю таблицу, заключается в том, что она подготовлена ​​для очертания, метод, в котором вы перемещаете связанные данные в другую базу данных (на другой машине); на основе определенных правил вы определяете, откуда взять информацию.

Вы можете перемещать данные следующими способами:

• разделите таблицу participation на основе поля uid1
• разделите таблицу messages на основе поля conversation_id

Обзор сообщений будет более сложным, так как вам, вероятно, придется делать два запроса; это может быть несколько смягчено кэшами (и в крайних случаях документами).

Надеюсь, это даст вам некоторые идеи относительно будущего планирования:)

Ответ 11

Думаю, вам не нужно создавать таблицу пользовательской беседы.

Если только пользователь может иметь только один разговор с кем-то, уникальный идентификатор для этого потока - это concat между userId и friendId. Поэтому я перемещаю столбец friendId в таблице usersmessage. Проблема порядка (friendId-userId - это тот же поток userId-friendId) может быть решена так:

SELECT CONCAT(GREATEST(userId,FriendId),"_",LEAST(userId,FriendId)) AS threadId

Теперь возникает проблема извлечения последнего сообщения после потока GROUP BY threadId.

Я думаю, что это хорошее решение, сделать concat между DATE и сообщением и после MAX в этом поле.

Я предполагаю, что для простоты дата столбца поле DATETIME ('YYYY-mm-dd H: i: s'), но это не нужно, потому что есть функция FROM_UNIXTIME.

Итак, окончательный запрос

SELECT 
        CONCAT(GREATEST(userId,FriendId),"_",LEAST(userId,FriendId)) AS threadId,
        friendId, MAX(date) AS last_date, 
        MAX(CONCAT(date,"|",message)) AS last_date_and_message 

FROM usermessages
WHERE userId = :userId OR friendId = :userId
GROUP BY threadId ORDER BY last_date DESC

результат поля last_date_and_message выглядит примерно так:

2012-05-18 00:18:54|Hi my friend this is my last message

он может быть просто проанализирован с вашего кода на стороне сервера.

Ответ 12

Расширение ответа, предложенного Наблюдателем.

Вам следует рассмотреть возможность отмены концепции "беседы" для дальнейшего упрощения.

+----+---------+------+------------------+--------+----------+
| id | message | read | time             | toUser | fromUser |
+----+---------+------+------------------+--------+----------+
| 1  |  test 1 |  0   | (some timestamp) |  3     |   4      |
| 2  |  test 2 |  0   | (some timestamp) |  4     |   3      |
+----+---------+------+------------------+--------+----------+

Список всех разговоров для пользователя 123:

SELECT * FROM (
    SELECT id, message, toUser, fromUser   
    FROM userMessages 
    WHERE toUser = 123 OR fromUser = 123 
    ORDER BY id DESC
) AS internalTable 
GROUP BY toUser, fromUser 

Перечислить весь разговор между пользователем 123 и пользователем 456:

SELECT * 
FROM userMessages
WHERE (toUser = 123 OR fromUser = 123) 
AND (toUser = 456 OR fromUser = 456)
ORDER BY time DESC

Ответ 13

Простейший способ, я думаю, для этого:

Таблицы:

conversation(cid | userId | friendId | last_message_id)
messages(mid | message | userId | read | time | cid)

Затем обновите last_message_id после того, как каждое сообщение будет вставляться пользователями в разговорном порядке.

И затем запустите этот простой запрос. Это даст вам то, что вы хотите.

SELECT * FROM conversation c, messages m 
WHERE (c.userId='$uid' OR c.friendId='$uid')
AND c.last_msg_id=m.message_id
ORDER BY created_time DESC

$uid - это идентификатор зарегистрированного пользователя.

Итак, в самом деле, что делает этот процесс:

• Отображение всех разговоров зарегистрированного пользователя.
• Ссылка на последнее сообщение (так что вам не нужна группа)
• И последнее отображение сообщений по порядку.