Currently I have a query that is getting a list of forum topics from a topics table. I also have a views table which stores when a registered user has viewed each topic.
What I want to do is select the last viewed time from the views table then count how many posts have a time less than that.
Is it possible to do this in 1 query?
This is my attempt but I’m not sure if this is possible. If it is then my Google skills are lacking.
LEFT OUTER
JOIN ( SELECT MAX(time) AS last_viewed
FROM views
WHERE user_id = '1'
GROUP BY
time ) AS views
ON views.fid = parents.fid
LEFT OUTER
JOIN (SELECT COUNT(pid)
FROM posts
WHERE posts.time < views.last_viewed
GROUP BY
posts.pid ) AS read_posts
ON read_posts.fid = parents.fid
With all the query’s where this would be applied I’m getting the latest post id, topic id and forum id (lp.tid) as a join from the post table. What I want to do is find out when the user last viewed that topic and count how many posts there were when they last read the topic, that way in PHP I can redirect to the right post.
CREATE TABLE IF NOT EXISTS `views` (
`view_id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`tid` int(11) NOT NULL,
`fid` int(11) NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`view_id`)
);
CREATE TABLE IF NOT EXISTS `forum` (
`fid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`fid`)
);
CREATE TABLE IF NOT EXISTS `posts` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT '0',
`time` int(11) NOT NULL DEFAULT '0',
`active` int(11) NOT NULL DEFAULT '1',
`tid` int(11) NOT NULL DEFAULT '0',
`fid` int(11) NOT NULL COMMENT 'forum id',
PRIMARY KEY (`pid`)
);
CREATE TABLE IF NOT EXISTS `topics` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(80) NOT NULL DEFAULT '',
`fid` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`tid`);
)
it’s tricky and requires careful thought, the time for which was in short supply around here until tonight, but the thunder/heat game hasn’t really got going, so i worked it out
you said you wanted to “count how many posts there were when they last read the topic” so please test this –
SELECT COUNT(*) AS posts_read
FROM ( SELECT MAX(time) AS latest_view_time
FROM views
WHERE uid = $uid -- selected user
) AS v
INNER
JOIN views
ON views.time = v.latest_view_time
AND views.uid = $uid -- selected user
INNER
JOIN posts
ON posts.tid = views.tid
AND posts.time <= views.time
i trust you have a test database comprehensive enough to test this properly?
SELECT forums.name AS `forum_name`
, topics.title
, topics.title
, topics.tid
, COUNT(*) AS posts_read
FROM forums
INNER
JOIN topics
ON topics.fid = forums.fid
INNER
JOIN ( SELECT tid
, MAX(time) AS latest_view_time
FROM views
WHERE uid = $uid -- selected user
GROUP
BY tid ) AS v
INNER
JOIN views
ON views.tid = v.tid
AND views.time = v.latest_view_time
AND views.uid = $uid -- selected user
INNER
JOIN posts
ON posts.tid = views.tid
AND posts.time <= views.time
WHERE forums.fid = 5
GROUP
BY topics.tid
One thing I did notice was that your query was selecting from “forums” rather than from “topics”, is that how I should be doing it or was it a mistake?