select max(paramValue) in set of rows grouped by chat_id

Question

I've seen couple of implementations to my issue around SO, but still can't tweak this to my needs.

// table:

CREATE TABLE `chat_msgs` (
`id` int(11) NOT NULL,
`chat_id` int(11) NOT NULL,
`user_to` varchar(70) NOT NULL, 
`user_from` varchar(70) NOT NULL,
`body` varchar(255) NOT NULL,
`msg_time` datetime NOT NULL,
`viewed` enum('0','1') NOT NULL DEFAULT '0' )
ENGINE=InnoDB DEFAULT CHARSET=utf8;

// dummy chat data:

INSERT INTO `chat_msgs` (`id`, `chat_id`, `user_to`, `user_from`, `body`, `msg_time`, `viewed`) VALUES
(46, 1, '40', '39', '我们商谈订单细节，好码?', '2018-03-05 01:06:38', '1'),
(47, 1, '40', '39', '45', '2018-03-05 01:06:56', '1'),
(48, 1, '39', '40', 'OK？', '2018-03-05 08:12:34', '0'),
(49, 2, '39', '211', 'jk', '2018-03-05 08:27:05', '1'),
(50, 2, '211', '39', 'hj', '2018-03-05 08:27:12', '1'),
(66, 2, '39', '211', 'fe', '2018-03-06 16:44:03', '0'),
(67, 3, '39', '160', 'sdfs sdfg fg', '2018-03-08 16:44:06', '0'),
(68, 3, '39', '160', 'are yoy here my friend?', '2018-03-09 08:44:06', '0');

Target: rows, where per each unique chat_id I have ONE LATEST (by time) row, containing: chat_id, user_to (user, to whom msg was sent), user_from (who sent msg), body (which is the message itself) and max(msg_time).

Essentially, it should output the latest chat msg logged-in user had with any other user.

I came up with:

SELECT * FROM  (SELECT chat_id,user_to,user_from,body,max(msg_time) FROM chat_msgs GROUP BY chat_id) t1  JOIN (SELECT chat_id,count(viewed) unviewed FROM chat_msgs WHERE viewed='0' AND user_to='39' group by chat_id) t2  on t1.chat_id=t2.chat_id

which does most part, except for the fact it does not return the latest 'body'.

I understand I gotta fit in a sub query for selecting right body, but somehow cant get to it. Thanks

score 0 · Answer 1 · answered Mar 09 '18 at 14:58

The following will work:

    select
    chat_msgs.id,
    chat_msgs.chat_id,
    chat_msgs.user_to,
    chat_msgs.user_from,
    chat_msgs.body,
    chat_msgs.msg_time,
    chat_msgs.viewed
from chat_msgs
left join chat_msgs next_latest on (
    (
        chat_msgs.user_to = next_latest.user_to and
        chat_msgs.user_from = next_latest.user_from
    )
    or
    (
        chat_msgs.user_from = next_latest.user_to and
        chat_msgs.user_to = next_latest.user_from
    )
    )
    and
    chat_msgs.msg_time < next_latest.msg_time
where next_latest.msg_time is null
and (chat_msgs.user_to = 39 or chat_msgs.user_from = 39);

This works by selecting all messages to or from the user and joining it to the message with a later send time, so all rows in the output will have the send time of the next message attached to them. The where clause then selects rows with null for the timestamp of the later message - that is, the messages without a message after them. In this instance there should only be one per chat thread.

Hi Simon, thanks for the comment. I am really trying to solve this without spending resources on additional checks, this method looks more complicated then what I would like to achieve. In my query, there is just a lack for a subquery that fetches right 'body' andthen joins on msg_time of first table = msg_time of that table in subquery. Just can lay this out properly. Thanks — Zerus, Mar 09 '18 at 16:06

Zerus · Answer 2 · 2018-03-12T14:45:51.903

Adding inner join worked things out. Also, I found myself in need of getting additional data from 'chats' table (it has 3 columns - 'request_id1' and 'request_id2' for each of respective users and 'chat_id'), so 1 more subquery was added:

SELECT t4.chat_id,t4.user_from,t4.user_to,t4.maxDate,t4.unviewed,t4.body,t4.ilocutor_uid,chats.rid1,chats.rid2 FROM 
        (SELECT t1.chat_id,t1.user_from,t1.user_to,t1.maxDate,t2.unviewed,t3.body,t3.ilocutor_uid 
        FROM 
        (SELECT chat_id,user_to,user_from,max(msg_time) maxDate FROM chat_msgs GROUP BY chat_id) t1 
        LEFT JOIN 
        (SELECT chat_id,count(viewed) unviewed FROM chat_msgs WHERE viewed='0' AND user_to=40 group by chat_id) t2 on t1.chat_id=t2.chat_id
        INNER JOIN (SELECT chat_id,body,msg_time, IF (user_to=40, user_from, user_to) ilocutor_uid FROM chat_msgs WHERE user_to=40 OR user_from=40) t3 
            on t1.maxDate=t3.msg_time) t4
    LEFT JOIN chats ON t4.chat_id = chats.chat_id ORDER BY t4.maxDate DESC

While I understand this is quite clumsy (sorry for that...^^), it works and gives exactly what I need.

What I am looking for still, is to decrease number of selects and joins by at least 1. There should be an option to omit additional select fetching 'body' of latest msg (currently, table "t4") + respective INNER JOIN.

This will work fine on older versions of MySQL. For it to work on recent versions, you must disable `ONLY_FULL_GROUP_BY` as described here: https://stackoverflow.com/questions/23921117/disable-only-full-group-by This method is also more complicated than my answer from an execution point of view; try profiling both methods to see the differences. — Simon Brahan, Mar 11 '18 at 10:38
Hey Simon and thanks again for comment. Your code is indeed faster, however, it does not output num of unviewed messages per each chat, that is the reason I can't currently accept your answer. If you could fix this up I would gladly accept your answer over my (quite clumsy unfortunately) code ^^, thank you! — Zerus, Mar 12 '18 at 14:32
Also, I have updated my answer to better reflect on situation. Hope this helps and thanks again for the help! — Zerus, Mar 12 '18 at 14:46

select max(paramValue) in set of rows grouped by chat_id

2 Answers2