php - 如何从mysql中获取未读消息

Question

消息被广播给用户。因此在更新表中只创建一条消息记录。

table name : updates
id : 
title : message
user_id : created user id

table name : message _status
id
message_id
user_id : read by user

如果任何用户阅读了消息，我们会在 updates_status 表中进行输入。

我想显示那些不是由当前用户创建和未读的未读消息列表。这是我的查询:

select u.*, s.user_id as status_user, s.id 
 from updates as u left outer join update_status as s ON u.id=s.update_id 
 where u.user_id != 1 and (s.user_id is null or s.user_id  != 1)

我得到 6 条记录，而不是 5 条。

这是来自 fiddle 的 DDL :

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `u_name`) VALUES
(1, 'Praveen'),
(2, 'Ravi'),
(3, 'Ram'),
(4, 'Mohan');

CREATE TABLE IF NOT EXISTS `updates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `updates`
--

INSERT INTO `updates` (`id`, `title`, `user_id`, `created`) VALUES
(1, 'message1', 2, '2015-09-28 15:50:38'),
(2, 'message2', 2, '2015-09-28 15:50:38'),
(3, 'message3', 1, '2015-09-28 17:00:37'),
(4, 'message4', 1, '2015-09-28 17:00:37'),
(5, 'message5', 3, '2015-09-28 17:05:21'),
(6, 'message6', 4, '2015-09-28 17:05:21');

CREATE TABLE IF NOT EXISTS `update_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `update_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `update_status`
--

INSERT INTO `update_status` (`id`, `update_id`, `user_id`, `created`) VALUES
(2, 1, 1, '2015-09-28 17:02:15'),
(3, 3, 2, '2015-09-28 17:04:16'),
(4, 1, 3, '2015-09-28 17:06:24'),
(5, 1, 4, '2015-09-28 17:06:33'),
(6, 3, 3, '2015-09-28 17:17:12');

Answer 1

更改为排除用户自己的更新

SELECT Updates.id 
FROM Updates 
JOIN Users 
ON Updates.user_id != Users.id and Users.id = 1 -- CURRENT USER ID
LEFT JOIN Update_status 
ON Users.id = update_status.user_id 
and updates.id = update_status.id 
WHERE update_status.created is null 
ORDER BY updates.created desc

返回

id
--
5
6
1