It is necessary to extract from the database all chats to which the user is subscribed, there is a trace. tables:

Subscriptions: chats_subscriptions

+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | uid | int(11) | YES | MUL | NULL | | | cid | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+

Chats: chats

 +--------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | owner | int(11) | NO | | NULL | | | label | varchar(100) | YES | | NULL | | | type | varchar(10) | NO | | private | | | status | int(1) | NO | | 1 | | | date | timestamp | YES | | CURRENT_TIMESTAMP | | +--------+--------------+------+-----+-------------------+----------------+

Messages: messages

 +-----------+-----------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | cid | int(11) | NO | | NULL | | | sender | int(11) | NO | | NULL | | | recipient | int(11) | NO | | NULL | | | date | timestamp | NO | | CURRENT_TIMESTAMP | | | content | text | NO | | NULL | | | del | int(11) | YES | | NULL | | | status | int(11) | YES | | NULL | | +-----------+-----------+------+-----+-------------------+----------------+

With the following query, I'm trying to get all the chats to which the user is subscribed, with the number of unread messages:

 SELECT `cs`.`cid`, `ch`.`owner` AS `cOwner`, `ch`.`label` AS `cLabel`, `ch`.`type` AS `cType`, `ch`.`status` AS `cStatus`, `ch`.`date` AS `cDate`, COUNT(`msg`.`id`) AS `countMsg` FROM `chats_subscriptions` `cs` LEFT JOIN `chats` `ch` ON `ch`.`id`=`cs`.`cid` LEFT JOIN `messages` `msg` ON `msg`.`cid`=`ch`.`id` AND `msg`.`del` IS NULL AND `msg`.`status` IS NULL AND `msg`.`recipient`=`cs`.`uid` WHERE `cs`.`uid` = 2

Such a request does not work quite correctly, it retrieves the information correctly, but if the user is subscribed to three chat rooms, only one line will return anyway. If you remove LEFT JOIN messages , then everything works correctly, but you need to retrieve the number of messages (I also want to add the text of the last unread message). Tell me, what cant?

And by the way, if in your opinion this is a curved structure of the database, I’ll be happy to hear your more optimal suggestions! (the task was this: to make correspondence in HP on the site with the ability to add / delete interlocutors, so I decided to present everything in the form of chats with subscriptions to them)

! UPDATE 3:

I tried to make such a request, everything seems to be correct, but it extracts only one line, although there are more subscriptions (chats from the user):

 SELECT cs.cid, u.login AS cLogin, u.ava AS cAva, ch.owner AS cOwner, lm.content AS lmContent, lm.lmDate AS lmDate, ch.label AS cLabel, ch.type AS cType, ch.status AS cStatus, ch.date AS cDate,COUNT(*) AS countMsg FROM chats_subscriptions cs INNER JOIN users u ON u.id=cs.uid INNER JOIN chats ch ON ch.id=cs.cid LEFT JOIN messages msg ON msg.cid=ch.id AND msg.del IS NULL AND msg.status IS NULL AND msg.recipient=cs.uid JOIN ( SELECT lm.id, lm.content, lm.cid, lm.date AS lmDate FROM messages lm WHERE lm.status IS NULL AND lm.date = ( SELECT MAX(date) FROM messages GROUP BY lm.id HAVING lm.id ) GROUP BY lm.cid ) lm ON lm.cid = cs.cid WHERE cs.uid = 2 GROUP BY cs.cid

Maybe somewhere is missing something or something extra? I can not understand...

  • @Mike, yes, you're right, I had to group it, thanks! I have added a question, can you give a detailed answer on the structure? and how to extract the last message (msg.content)? - sanu0074
  • @Mike I added LEFT JOIN messages last_msg ON last_msg.cid = cs.cid AND last_msg.status IS NULL and in select - MAX (fx_last_msg.date) but the first message is displayed but not the last. Through left join it should work? Or there it is necessary to do join (and somehow here select ....)? - sanu0074
  • @Mike updated the question with the query the cat I am doing and what I can’t do. If you can reply in reply, I will upload it) - sanu0074
  • @Mike still does not work correctly: hostingkartinok.com/… - sanu0074

1 answer 1

Summing up the correspondence:

  1. When using at least one group function in a request (for example, count ()), by default all data is grouped up to a single record. Therefore, it is necessary to set the group by clause to get the data in the right section.
  2. According to the database structure: The "recipient" field in messages, if used, is only for private messages, outside chat rooms. In the table of subscriptions (chats_subscriptions) I propose to add the field "last viewed date" ( seen_date ) in which to fix which of the subscribers when he last read the room. The status field is likely to become unnecessary after that.
  3. The msg.del field from the messages table is msg.del to the table “deleted messages” with the create table msg_delited( msg_id int not null, uid int not null, primary key (msg_id,uid) ) structure create table msg_delited( msg_id int not null, uid int not null, primary key (msg_id,uid) )

  4. Request receiving, among other things, the last message from the chat looks something like this

      SELECT `cs`.`cid`, `ch`.`owner` AS `cOwner`, `ch`.`label` AS `cLabel`, `ch`.`type` AS `cType`, `ch`.`status` AS `cStatus`, `ch`.`date` AS `cDate`, COUNT(`msg`.`id`) AS `countMsg`, -- Общее кол-во сообщений sum( if(cs.seen_date < msg.date, 1, 0) ) as countNew, -- Кол-во непрочитанных сообщений substr(max(concat(msg.date,msg.content)),20) as content, -- Текст последнего сообщения ( exists( select 1 from chats_subscriptions sub where sub.cid=cs.cid and sub.seen_date>=max(msg.date) limit 1 ) ) as chSeen -- Кто то уже видел последнее сообщение FROM `chats_subscriptions` `cs` JOIN `chats` `ch` ON `ch`.`id`=`cs`.`cid` LEFT JOIN `messages` `msg` ON `msg`.`cid`=`ch`.`id` LEFT JOIN msg_delited md ON md.msg_id=msg.id and md.uid=cs.uid -- <<--- Признаки "удаленных" только для текущего пользователя -- AND `msg`.`del` IS NULL AND `msg`.`status` IS NULL <<--- скорее всего не нужно -- AND `msg`.`recipient`=`cs`.`uid` <<--- Это уже не нужно WHERE `cs`.`uid` = 2 AND md.msg_id is NULL -- <<--- Получаем только НЕ удаленные сообщения group by cs.cid

Alarmed by checking some "recipient of the message." In view of the described task with a chat room with several speakers, it is not clear who is the recipient of the message. In theory, there may be a sender, but all who are signed up for a conversation receive messages (if we are in chat mode, not personal message). So the recipient field would be logical to leave NULL. And when counting messages in the room, you probably need to take into account all the messages. Or for example with recepient is null, that would separate completely personal.

  • Comments are not intended for extended discussion; conversation moved to chat . - Nick Volynkin ♦