CODEWITHSUNDEEP
mysqlsql
bjk116
bjk116

Reputation: 529

How to get the last message in a thread SQL Query

I have two tables, listhdthreads and listhdmessages. The listhdthreads is the parent table, and each thread has messages in it as children.

CREATE TABLE `listhdthreads` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `fromUser` int(11) NOT NULL,
  `respondingUser` int(11) DEFAULT NULL,
  `attachments` varchar(300) DEFAULT NULL,
  `customerId` int(11) NOT NULL,
  `locationId` int(11) DEFAULT NULL,
  `areaId` int(11) DEFAULT NULL,
  `assetId` int(11) DEFAULT NULL,
  `projectId` int(11) DEFAULT NULL,
  `contactId` int(11) DEFAULT NULL,
  `title` int(11) NOT NULL,
  `priority` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `ddGroup` int(1) DEFAULT NULL,
  PRIMARY KEY (`idx`)
)

and

CREATE TABLE `listhdmessages` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `parentThreadId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `sendingUser` int(11) NOT NULL,
  `text` varchar(300) NOT NULL,
  `adminMessage` bit(1) DEFAULT b'0',
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `parentId` (`parentThreadId`),
  CONSTRAINT `listhdmessages_ibfk_1` FOREIGN KEY (`parentThreadId`) REFERENCES `listhdthreads` (`idx`) ON DELETE CASCADE ON UPDATE CASCADE
)

Now I have this current query which populates a tabl with all the threads and I want to include the last time a message was sent (which I already have), and the content of the last message itself (this is what I need help with).

The current query as it stands is this (removed unnecessary columns selected)

SELECT MAX(m.createdAt) as latestMessage,
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId 
LEFT JOIN listcustomers c ON hd.customerId = c.idx 
LEFT JOIN listlocations l ON hd.locationId = l.idx 
LEFT JOIN listprojects lp ON hd.projectId = lp.idx
LEFT JOIN listusers lu ON hd.contactId = lu.idx 
LEFT JOIN listusers lus ON hd.fromUser = lus.idx 
LEFT JOIN listusers lusu ON hd.respondingUser = lusu.idx
LEFT JOIN deschdtitles t ON hd.title = t.idx 
LEFT JOIN deschdpriorities p ON hd.priority = p.idx 
LEFT JOIN deschdstatus s ON hd.status = s.idx 
LEFT JOIN helpmessageparticipants hmp ON hd.idx = hmp.parentThreadId
GROUP BY hd.idx

I am at a loss with trying to get get the latest message itself. How can I go about that? I am using MySQL 5.6.

EDIT: Sorry, I forgot to include the group by which is necessary for the rest of the query to work right. That's what is giving me a headache.

Edit 2: Figured it out. Turned my initial query into a subquery that I just order by in such a way that my top row of each thread is exactly what I want, and then group by outside of it.

SELECT * FROM
(SELECT hd.idx, hd.ddGroup, c.name as customer, IF(hd.locationId=-1,"None",l.name) as location, CONCAT(lu.firstname, ' ', lu.lastname) as contactId, t.title, lp.number, 
UPPER(CONCAT(SUBSTRING(lus.firstname,1,1), SUBSTRING(lus.lastname,1,1))) as createdBy, IF(hd.respondingUser=-1,"---",UPPER(CONCAT(SUBSTRING(lusu.firstname,1,1), SUBSTRING(lusu.lastname,1,1)))) as forUser,
p.priority,s.status,hd.customerId,hd.locationId,hd.areaId,hd.assetId,hd.projectId,
m.createdAt as latestMessage, m.text as LastMessage
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId 
LEFT JOIN listcustomers c ON hd.customerId = c.idx 
LEFT JOIN listlocations l ON hd.locationId = l.idx 
LEFT JOIN listprojects lp ON hd.projectId = lp.idx
LEFT JOIN listusers lu ON hd.contactId = lu.idx 
LEFT JOIN listusers lus ON hd.fromUser = lus.idx 
LEFT JOIN listusers lusu ON hd.respondingUser = lusu.idx
LEFT JOIN deschdtitles t ON hd.title = t.idx 
LEFT JOIN deschdpriorities p ON hd.priority = p.idx 
LEFT JOIN deschdstatus s ON hd.status = s.idx 
LEFT JOIN helpmessageparticipants hmp ON hd.idx = hmp.parentThreadId
ORDER BY hd.idx, m.createdAt DESC) as t
GROUP BY t.idx

Upvotes: 0

Views: 127

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can use a correlated subquery in the ON clause. In the subquery you get the last (MAX) idx of messages from the thread.

SELECT hd.*, m.* -- select columns you need
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON m.idx = (
    SELECT MAX(mmax.idx)
    FROM listhdmessages mmax
    WHERE mmax.parentThreadId = hd.idx
)
-- JOIN more tables

It is better and simpler to use the AUTO_INCREMENT column than createdAt. The fact that createdAt is not UNIQUE, and two messages from the same thread can be posted at the same second, is reason enough.

Upvotes: 1

kheengz
kheengz

Reputation: 1038

SELECT m.createdAt as latestMessage
FROM listhdthreads as hd
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId
...
GROUP BY m.parentThreadId
ORDER BY m.createdAt DESC LIMIT 1

OR

SELECT hd.idx, m.createdAt as latestMessage
FROM listhdthreads as hd
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId
...
GROUP BY hd.idx, m.createdAt
ORDER BY m.createdAt DESC LIMIT 1

Upvotes: 0

Related Questions