I have a chat system and I want to show messages sent in the last hour, but I also want to show the last 20 messages no matter how long ago they were sent.
Is there a way I can do this in.a SQL query?
CREATE TABLE IF NOT EXISTS `chat` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT(11) UNSIGNED NOT NULL,
`item_id` INT(11) UNSIGNED NOT NULL,
`message` TEXT NOT NULL,
`recipient` INT(11) NOT NULL DEFAULT '0',
`type` ENUM ('message', 'announcement') NOT NULL DEFAULT 'message',
`channel` ENUM ('general', 'private') NOT NULL DEFAULT 'general',
`posted` DATETIME NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `posted` (`posted`),
KEY `type` (`type`),
KEY `channel` (`channel`),
KEY `recipient` (`recipient`)
)
ENGINE = MyISAM
DEFAULT CHARSET = `utf8`
AUTO_INCREMENT = 2;
This should do the trick:
(
select * from chat
where timestamp > DATE_SUB(now(), interval 1 hour)
)
union
(
select * from chat order by posted desc limit 20
)
order by posted
Explanation:
So, when there is no data in the past hour, you'll still get the 20 most recent posts. If there's a lot of data in the past hour, you'll get all of those.
Try this:
SELECT *
FROM `chat`
ORDER BY `posted` DESC
LIMIT 0,20;
It will list the last 20 rows (by date/time). Since you want older chats when there aren't enough within the last hour, you don't need to worry about the age of the chats.
The following is untested and may have a syntax error, so you may have to play with it a bit.
SELECT *
FROM `chat`
WHERE DATE_ADD(a.`posted` interval 1 hour) <= NOW()
UNION
(
SELECT *
FROM `chat`
WHERE a.posted > DATE_SUB(NOW(), interval 1 hour)
AND (SELECT count(*) FROM `chat` WHERE a.posted <= DATE_SUB(NOW(), interval 1 hour)) < 20
ORDER BY `posted` DESC
LIMIT 0,20)
ORDER BY `posted` DESC
It should only add the posts older than the last hour when there are less than 20 posts in the last hour.