0x00 Posted October 27, 2015 Share Posted October 27, 2015 I use a forum just as an example here, it could be anything. What is the best / general way to log / monitor views of a thread (i.e. when is a new view new? e.g. after a new post, etc) and to display that there is a new post since a specific users last view. BTW, I'm not after any code, just the concept. Yes I can imagine how to basically do it using brute force, but was wondering if there is an efficient way I hadn't thought of? Thanks Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 There's no real super efficient way to tackle this. You basically have to have one write per read. When a user views a post, you store that in some way. Quote Link to comment Share on other sites More sharing options...
0x00 Posted October 27, 2015 Author Share Posted October 27, 2015 I have a single log for everything, which includes user_id, link and time (amongst other). So I was working on searching by link with uid and time being used as factors. Its just if there's 30 threads on a page this sounds like a lot of work, esp if the forum was busy... So I wasn't sure if each page had its own log of some sort may be somehow more efficient... Thankyou Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 It's been a while since I have played with forum software, so I don't remember details off the top of my head. One way is you would have a "users" table and a "topics" table. Then you would have a "viewed_topics" table (or whatever you want to call it). "viewed_topics" would just have columns for the user id, topic id, and maybe a date or post id. When a user views the topic a row gets added to "viewed_topics". When a new post is created, all rows in "viewed_topics" get deleted. When displaying topics, you can just JOIN the "viewed_topics" table and determine if it is new or not by whether a row exists there or not. 1 Quote Link to comment Share on other sites More sharing options...
0x00 Posted October 27, 2015 Author Share Posted October 27, 2015 I like it And keep a tally of actual views in the topics table Quote Link to comment Share on other sites More sharing options...
printf Posted October 27, 2015 Share Posted October 27, 2015 Wow, tables would be filled with tons of junk, not very practical if you ask me. it sort of like the "glass half filled theory", if you start with "posts / topics not viewed", it's like the glass is half full, but when the user starts reading a lot of posts / topics, the database will soon become bloated and the glass half full will become half empty, (just to tell them what topics or post they haven't viewed), like I said, it's not very practical. I always recommend the minimum, highlight posts and topics that are new since they last visited. You know thinking about things like this makes me kind of laugh at developers who waste run-time resources on such silliness when their code base could use a lot of improvement. Its like the other crazy stuff most forum software have, one example... "You have spent 43 days, 7 hours, 14 minutes, and 7 seconds on our forum", to me that is simply stupid, because you cannot maintain 'state', so their implementing a 'where just guessing' is stupid, (period) And yes, I know I am very opinionated, but having spent countless hours modifying forum software for my many clients, I do know what I am talking about! Quote Link to comment Share on other sites More sharing options...
0x00 Posted October 27, 2015 Author Share Posted October 27, 2015 Wow, tables would be filled with tons of junk, not very practical if you ask me. it sort of like the "glass half filled theory", if you start with "posts / topics not viewed", it's like the glass is half full, but when the user starts reading a lot of posts / topics, the database will soon become bloated and the glass half full will become half empty, (just to tell them what topics or post they haven't viewed), like I said, it's not very practical. I always recommend the minimum, highlight posts and topics that are new since they last visited. You know thinking about things like this makes me kind of laugh at developers who waste run-time resources on such silliness when their code base could use a lot of improvement. Its like the other crazy stuff most forum software have, one example... "You have spent 43 days, 7 hours, 14 minutes, and 7 seconds on our forum", to me that is simply stupid, because you cannot maintain 'state', so their implementing a 'where just guessing' is stupid, (period) And yes, I know I am very opinionated, but having spent countless hours modifying forum software for my many clients, I do know what I am talking about! So you'd say we're better off just using the existing logs and base the results off them? These features are sort of expected nowadays and I know I like them, they're even on here Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 (edited) I always recommend the minimum, highlight posts and topics that are new since they last visited. I've used a ton of different forum software over the years, and I can't think of one that works that way. If you do it that way, new posts will only be new until you refresh the page. Or, you have some kind of offset where it remains new for X minutes. Most forum software, like this one, will maintain state cross-device. So if I login on my PC and there are 10 new threads, and I read one, and then I login on my phone there will only be 9 new threads. You could prune the table when data gets so old if you desire. It's an expensive feature. EDIT: And I think you've got it backwards. You don't track what they haven't read, you track what they have explicitly read. Edited October 27, 2015 by scootstah Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted October 27, 2015 Share Posted October 27, 2015 Besides that, handling “large” amounts of data efficiently is the whole point of a database system. If you experience performance issues, that's most likely due to poorly written queries or missing indexes. What does “large” even mean in this context? 1,000 rows? 10,000 rows? 100,000 rows? That's laughable. Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 What does “large” even mean in this context? 1,000 rows? 10,000 rows? 100,000 rows? That's laughable. This forum has 166,826 members and 1,515,137 posts. It doesn't tell me how many topics. So worst case scenario, that's 252,764,245,162 rows, ha. But, it would be easy enough to keep that table pruned and at a reasonable size. There were 16,296 total topic views for this month. 314,000 for the year. So, yeah, it's completely manageable. Quote Link to comment Share on other sites More sharing options...
printf Posted October 27, 2015 Share Posted October 27, 2015 So you'd say we're better off just using the existing logs and base the results off them? These features are sort of expected nowadays and I know I like them, they're even on here Having features in your software that don't reflect the true facts while using CPU clocks to support that feature is stupid. Let me give you a better example. Most people I know use Google Maps to get directions to go everywhere. And like you stated, 'features like that are pretty cool features', they give you that wow factor! But would you give your '12 year old child' a phone and tell them to use Google Maps to navigate wherever they are wanting to go, I WOULDN'T, why, because your child just might die believing Google Maps is a safe mapping program when it's not, and no that isn't entirely Google's fault, but it is partly their fault, because they don't personal audit every bit of their mapping data. Now, you might say, what does that have to do with what we are talking about, not a lot, but my point is simple, you shouldn't put things out there that just guess at stuff just because you think it's cool. And just so you know, people have gotten lost, even died trusting Google maps. Anyway, back to what I was really wanting to say... My problem isn't so much with the silly features some developers implement, but I do have a problem with how they are implemented. Things like, who's online, total topics, total posts, etc; should not be handled in your script, as they are things that change when another action takes place. Say you add a new topic, that creates a new topic and a new post, so you have to update you totals, (topics & posts) so you run a query or two to do that, instead of using the database to do it for you, trigger(do this), which makes no sense to me. Now compound that simple developers mistake X 10, and you will end up doing (10,000) unneeded things on a forum that generates (1000) new topics / posts a day. That to me tells me whoever developed that software is not a developer, to me they are just a script kiddie doing things in most illogical way... Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 How is it a "silly feature"? That is the way every forum package ever works. Quote Link to comment Share on other sites More sharing options...
printf Posted October 27, 2015 Share Posted October 27, 2015 EDIT: And I think you've got it backwards. You don't track what they haven't read, you track what they have explicitly read. I didn't say I would do it one way or the other, I just used that as an example, and really how it is done doesn't matter, what matters is the result, which loads a database with junk. Do you really believe a user is interested in topics and posts that are years old... Hi printf, welcome back, did you know you forgot to read a post from 5 years ago... lol! Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 Hi printf, welcome back, did you know you forgot to read a post from 5 years ago... lol! You're missing the point entirely. Again, you're talking about tracking what someone didn't read. Quote Link to comment Share on other sites More sharing options...
printf Posted October 27, 2015 Share Posted October 27, 2015 You're missing the point entirely. Again, you're talking about tracking what someone didn't read. didn't read, huh! Didn't read, new replies, new posts, new topics are one in the same to me. Since a users last visit, a user will encounter any of those references, if they are implemented. and a new topic, new post, new reply == a not read / unseen, new topic, new post, or new reply since their last visit. I don't believe I am missing the point, as giving a user the opportunity to know what is new / unseen / unread is a necessity but you have to in someway draw the line where that feature becomes impractical. I mean, it's not... see how smart I am, I know what you have viewed or not viewed because I am endlessly tracking you, it's simply giving them the ability to know what's has been going on since their last visit! Quote Link to comment Share on other sites More sharing options...
scootstah Posted October 27, 2015 Share Posted October 27, 2015 The idea is that you store which topics they have viewed in the database. Within a given time frame, if a topic does not have a row in that "viewed" table, then it is new. Very simple, nothing complicated here. This is a feature that every forum package within the last decade or more has implemented, most of which in this very same way. That's just how it works. You could track it client-side if you wanted, but then you don't have persistence cross-device. At that point you have to decide what your end goal is. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.