Вопрос по sql, innodb, database, mysql, database-design – Есть ли более простой способ добиться этого стиля обмена сообщениями пользователей?

28

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

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

Пользователь может иметь только один разговор с другим пользователем.

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

У меня есть две таблицы:

userconversation usermessages

userconversation

Содержит идентификатор автоинкремента, который является идентификатором разговора, а также идентификатор пользователя и идентификатор друга.

Кто бы ни инициировал первый разговор, он всегда будет userId и friendId получателя, тогда он никогда не изменится для этого разговора.

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

usermessages

Содержит конкретные сообщения, а также флаг чтения, время и идентификатор разговора

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

How it works

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

Where it gets complicated

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

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

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

Я просто не думаю, что это самый лучший способ, которым это можно сделать, но не могу придумать, как другие тоже подходят к нему?

Таблица базы данных - это InnoDB для ускорения соединений и улучшения целостности данных, поэтому у меня не может быть двух строк с автоинкрементом.

Есть ли другой способ, которым я мог бы избавиться от таблицы разговоров пользователей и создать уникальный идентификатор, чтобы поместить в столбец разговор? Затем я мог бы просто переместить userId и friendId в сообщения пользователя ... но это создаст много избыточных данных?

@ Silver89: ОК, смотрите обновление. Хороший пост, кстати. Quassnoi
Вы не отслеживаете отправителей каждого сообщения здесь? Для разговора между Алисой и Бобом, как вы узнаете, кто отправил какое сообщение? Или два направления ведут два отдельных разговора? Quassnoi
теперь это то, что я называю хорошим объяснением. немного сложнее, но хорошо объяснил. +1 itachi
@Quassnoi Да, я пропустил этот столбец, обновил. Dan
Хорошо написанный вопрос, браво, сэр. afuzzyllama

Ваш Ответ

13   ответов
1

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

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

Теперь существует проблема получения последнего сообщения после GROUPI threadId.

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

Для простоты я предполагаю, что столбец date является полем 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

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

1

проверено и широко используется пользователями Arutz Sheva -http://www.inn.co.il (Иврит).

create a "topic" (conversation) table:

  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 link between user and conversation:

    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 a message

    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 - inbox, последнее сообщение - кто угодно, кроме меня, а Inbox - наоборот. LIMIT используется для пейджинга.

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

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

BCC - это только одно поле, используемое для его пометки. просто пропустите это.
Это кажется немного сложным, учитывая, что у нас нет скрытой или тематической информации. Это больше похоже на службу прямых сообщений твиттеров. Dan
6

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

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

вот мои данные испытаний:

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

Если это не так ... просто проигнорируйте мой ответ: P

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

@ Silver89, вы не можете указать порядок внутри группы, но в этом случае вы можете отсортировать данные после группировки. Единственный шаг, который вам нужно сделать сейчас, это объединить результат сusermessages таблица для получения самих сообщений (группировка поusermessage.id а затем присоединиться к той же колонке).
Это не сработает так, потому что вы не можете упорядочить сгруппированные результаты, в идеале это был бы идеальный сценарий. Dan
1

Table details
conversations (#id, last_message_id)

participation (#uid1, #uid2, conversation_id)

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

conversations

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

participation

Эта таблица записывает разговоры между двумя пользователямиin both directions; Чтобы объяснить почему, взгляните на следующий ключ:

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 также оптимизирует поиск идентичности в первой части вашего ключа. Третий также дает довольно хорошую производительность, поскольку вторую часть вашего ключа можно использовать для запросов диапазона. Последний запрос не работает вообще, потому что индекс "смещен влево" и, следовательно, он выполняет полное сканирование таблицы.

messages

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

Operation

sending messages

Чтобы определить, был ли уже установлен диалог между двумя пользователями, вы можете просто запросить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 сообщения VALUES (: journal_id, 0,: sender_id,: message_contents, 0, NOW ());     ОБНОВЛЕНИЕ разговоров SET last_message_id = LAST_INSERT_ID () WHERE id =: разговор_ид

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

conversation overview

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

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

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

Optimization

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

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

divide the participation table up based on the uid1 field divide the messages table up based on the conversation_id field

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

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

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
1

поскольку у меня нет доступа к 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
1

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

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

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

SELECT * FROM usermessages 
WHERE (userto = :userto OR userto = :userfrom) 
AND (userfrom = :userfrom OR userfrom = :userto) 
ORDER BY timecreated DESC 
LIMIT 10
Итак, как лучше всего подойти к нему, если вы ожидаете такого количества сообщений? Dan
Посмотрите в моем ответе - очень долго, но проверено и работает с большой и тяжелой системой.
Будет работать до тех пор, пока у вас не появится 150K сообщений и БД не станет загруженной и медленно. Я проверял! :-)
4

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

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

+----+---------+------+------------------+----------------+--------+----------+
| 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, Одно ограничение этого дизайна - то, что только 2 пользователя могут быть отделены от разговора.

Update

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

SELECT id, message 
FROM userMessages 
WHERE conversationId = {conversationId} 
ORDER BY time DESC 
LIMIT 1
@ Silver89 Обновленный ответ
Ах да, это кажется разумным решением! Dan
Так что, если последний пользователь ответил на сообщение «Тест 2» Будет ли это с параметром разговора 2 или 1?
@Tim Я думал, что иерархия идет только на 2 уровня (сообщение о начале разговора, а затем все последующие сообщения), поэтому ответ на ваш вопрос будет 1. Однако это не исключает возможности выбора другой стратегии (то есть вы может иметь более глубокое дерево, если хотите)
Сможете ли вы получить последнее сообщение в разговоре, используя этот подход? Скажем, если вы хотите перечислить все сообщения в папке входящих сообщений с последними 50 символами последнего сообщения в беседе, можно ли это сделать? Dan
0

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

+----+---------+------+------------------+--------+----------+
| 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
Небольшая настройка на запрос. Как насчет этого?
Группировка по не работает, потому что порядок выполняется для сгруппированного элемента (самый старый идентификатор) и, таким образом, возвращает неправильные строки. В противном случае это был бы идеальный способ сделать это. Dan
1

www.infinitbin.com, Я разработал собственный Infinitbin. У него есть две базы данных, как у вас тоже. таблица входящих сообщений: -

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

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

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

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

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

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

добавьте в таблицу последний идентификатор сообщения.userconversation :

ALTER TABLE userconversation ADD lastusermessageid

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

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
ваши коды лучше, чем другие. потому что он линейный. также вы могли бы использоватьTrigger заuserconversation Обновить.
Хорошая идея. Резервное хранение ссылки на последнее сообщение в беседе значительно повысит производительность при большом количестве данных. Это упрощает сортировку разговоров по последним действиям или, например, предварительный просмотр последнего сообщения на дисплее.
@MajidTaheri: Использование триггера - хорошая идея, если таблица может быть изменена более чем одним фрагментом кода. Однако триггеры могут не поддерживаться используемой версией mysql.
3

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

enter image description here

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

It assumes messages between same two users cannot be generated more frequently than the resolution provided by the type used for SEND_TIME.1 The direction of the message is not determined by order of USER1_ID and USER2_ID, but with a separate flag (DIRECTION). This way, a message between given users will always have the same combination of USER1_ID and USER2_ID (enforced by the CHECK above), regardless of who sent and who received the message. This greatly simplifies querying. It is unfortunate that all InnoDB tables are clustered, so the secondary index I1 is relatively expensive. There are ways to work around that, but the resulting complications are probably not worth it.

С этой моделью данных становится довольно легко сортировать «разговоры» (определяется парой пользователей) по последнему сообщению. Например (заменить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 is the reason for the secondary index 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.

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

0

Tables:

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 - это идентификатор зарегистрированного пользователя.

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

Displaying all conversation of logged in user. Referencing last message (so you do not need group by) And last displaying messages in order by desc.

Похожие вопросы