Complicated sql-request misappropriately extracts data



  • All the chat rooms signed by the user have to be removed from the bud.

    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 |                |
    +--------+--------------+------+-----+-------------------+----------------+
    

    Communications: 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              |                |
    +-----------+-----------+------+-----+-------------------+----------------+
    

    Next request, I'm trying to get all the chat rooms signed by the user, with a ring of unreaded 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
    

    It's not exactly the right thing to do, he's retrieving the information right, but if the laser is signed for three reads, it's gonna be just one line. That's if you remove LEFT JOIN messages - Then it's working correctly, but it's necessary to extract a number of messages. Tell me, what's the slice?

    And by the way, if you think it's a curve of hell, I'd love to hear your best suggestions!

    ! UPDATE 3:

    Attempted to make such a request, it's all right, but only one line is extracted, although the subscriptions (user chats) are bigger:

    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
    

    Is there somewhere missing something or something? I can't figure it out. ♪ ♪



  • In summary of the correspondence:

    1. When using at least one group function (e.g. count()) on default, all data shall be grouped to the only record. Therefore, a proposal must be made. group by to obtain data in the right cut.
    2. On the structure of the OBD system, the "recipient" field in messages, if used, only for private messages, outside the chat room. I'd like to add the field "date of last view" to the subscription table (chats_subscriptions)seen_datein which to record which of the subscribers when Last read the room. The field of status is likely to become unnecessary after that.
    3. Field msg.del from the table of messages to the " completed messages " with the structure create table msg_delited( msg_id int not null, uid int not null, primary key (msg_id,uid) )
    4. The request from the recipient, among other things, is that the last chat message looks like it.

       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
      

    I've been looking into someone who's got a message. In view of the task described with a chat room with several speakers, it is not clear who is the recipient of the communication. There may be a consignor, but we get messages (if we're in a chat mode, not a personal message) to anyone who's signed for conversation. So, the field. recipient It would be logical to leave NULL. And when the messages are counted in the room, it must be taken into account. Or, for example, recepient is null to separate completely personal.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2