* The "view older posts" link in the Locker Room brings you to older posts on the main site
* There is no longer an archive by month or category that I can find
* I miss the search engine
All of these are closely related to me wanting to reread the classic Red Dawn thread.
rcade: I gotta figure out how Matt did it without crushing his database
yerfatma: I always wondered that too. If you figure it out, can you provide details? There's really nothing you can cache. I think the front page of Metafilter is static for non-logged-in users, which would relieve a lot of strain on the db.
I think you're right that the front page is cached for non-logged-in users. As for the new comments indicators, if I recall correctly, there was a cookie in previous SpoFi containing a datetime for the last visit. I'm assuming Matt used a single query such as
SELECT post_id, id FROM comments WHERE published_date > current_user.last_visit ORDER BY post_id ASC, id ASC
The result count of this query would yield the total of new comments. Then you only need a couple of loops in CF/PHP/whatever to get the first new comment and the number of new comments for each post. It's a single query on a single table, the least strain you can put on the db.
Yeah, but you don't want to run a query for every post. Plus your query would only return posts with new comments and older vewrsions of MySQL don't support sub-queries so it's something more like:
SELECT p.id, p.title, p.body, p.create_date, COUNT(all.id) as all_comments, COUNT(new.id) as new_comments
FROM posts p
INNER JOIN comments all
LEFT OUTER JOIN comments new
WHERE new.published_date > current_user.last_visit
ORDER BY p.create_date DESC
LIMIT 0,20
But that feels naive to me. It's the kind of thing that works fine but I'd love to have a more efficient way of getting those numbers. but every single logged in user has a different last visit time. So you're back to hammering the db.
And, of course, I left off the relevant joins for the two comments tables to tie them to the posts. Brilliant.
I stayed away for a couple of weeks, but I don't have a history anymore either. Has my years-long slate been wiped clean?
Will there be a new profile page feature? It was fun to keep track of all the fantasy results and read others sport bios. While my number was rather large and my history somewhat short, it is a feature I would like to see back. I also enjoyed the search engine for the archives.
Does the new SportsFilter plan to recreate those features? Curious what's in store.
Yeah, but you don't want to run a query for every post
No, I don't. I forgot to say that I'm running my new comments query on top of the regular (non-logged-in) front page query, which should be fairly straightforward (apart from regular 'Oh, right, MySQL doesn't support this' drudgery).
Then the *new* markers are just 'joined' in code when outputting the page. You could even conceivably just parse the cached version of the page to add these markers, and forego the initial query.
I'm absolutely not certain I'm being clear here...
