Reputation: 941
I have a messaging system which has the tables "message" which just contains the "subject" then "message_user" which contains the message body, who sent it, who its for and whether its deleted / unread.
#Message Table
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `message` (`id`, `subject`)
VALUES
(1, 'Test'),
(2, 'Test Again');
#Message User Table
CREATE TABLE `message_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`interlocutor` int(11) DEFAULT NULL,
`body` text,
`folder` enum('inbox','sent') NOT NULL,
`starmark` tinyint(1) NOT NULL DEFAULT '0',
`unread` tinyint(1) NOT NULL DEFAULT '1',
`deleted` enum('none','trash','deleted') NOT NULL DEFAULT 'none',
`date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `message_user` (`id`, `message_id`, `user_id`, `interlocutor`, `body`, `folder`, `starmark`, `unread`, `deleted`, `date`)
VALUES
(1, 1, 1, 2, 'Hi, how are you?', 'sent', 0, 1, 'none', '2018-10-23 09:36:02'),
(2, 1, 2, 1, 'Hi, how are you?', 'inbox', 0, 1, 'none', '2018-10-23 09:36:02'),
(3, 1, 2, 1, 'I am good thanks, you?', 'sent', 0, 1, 'none', '2018-10-23 09:46:02'),
(4, 1, 1, 2, 'I am good thanks, you?', 'inbox', 0, 1, 'none', '2018-10-23 09:46:02'),
(5, 2, 1, 3, 'Hi!', 'sent', 0, 1, 'none', '2018-10-23 09:50:22'),
(6, 2, 3, 1, 'Hi!', 'inbox', 0, 1, 'none', '2018-10-23 09:50:22');
I wrote the following query:
SELECT
*
FROM message m
JOIN message_user mu ON m.id = mu.message_id
WHERE mu.deleted = 'none'
AND mu.user_id = 1 #user_id of person checking messages
ORDER BY mu.id DESC;
But this is currently returning 3 rows even though there is only two conversations. I tried to GROUP BY but it still showed 3 rows.
I would expect the first two rows in the above example not the last one.
I want the query to return a list of the conversations with the latest message which has been sent which I (user_id) am involved in.
Upvotes: 1
Views: 150
Reputation: 28874
Row_number()
; otherwise the solution would have been much verbose, using Session variables.m.id
, we will determine the row number values. Row number values will be ordered in descending order of date
.Date
is a keyword in MySQL. You should avoid naming column/table using it. Still if you have to do so, you will need to use backticks around it.Try the following (DB Fiddle DEMO):
SELECT dt.*
FROM (
SELECT m.id,
m.subject,
mu.id AS message_user_id,
mu.message_id,
mu.user_id,
mu.interlocutor,
mu.body,
mu.folder,
mu.starmark,
mu.unread,
mu.deleted,
mu.`date`,
Row_number()
OVER (PARTITION BY m.id
ORDER BY mu.`date` DESC) AS row_no
FROM message m
JOIN message_user mu
ON m.id = mu.message_id
WHERE mu.deleted = 'none'
AND mu.user_id = 1 ) AS dt
WHERE dt.row_no = 1
ORDER BY dt.id DESC
Upvotes: 1
Reputation: 3440
Try this :
select
m.id as id_message, m.subject as subject_message,
mu.id as id_message_user, mu.interlocutor, mu.body, mu.folder, mu.starmark, mu.deleted, mu.date
from message as m
inner join message_user as mu on mu.message_id = m.id and mu.deleted = 'none' and mu.user_id = 1
group by id_message
order by id_message_user desc
I removed
http://sqlfiddle.com/#!9/91a5e4/15
Upvotes: 0