What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

Does SO store post activity in a table? I'm asking because I notice, on the main page, the owner of the last activity is listed instead of the owner of the question.

The posts table, as far as I can tell, does not have a field for (in the data dump at least) lastActiveUserId ...

So how do you figure out who the last active use is?

share|improve this question
    
@unhelpful down voter, it would be nice to explain why you think this post merits a down vote. I see no harm in the question. –  Mohamad Jun 12 '11 at 14:42

2 Answers 2

up vote 2 down vote accepted

In the Posts table, there are:

  • LastEditorUserId
  • LastEditorDisplayName
  • LastEditDate
  • LastActivityDate

and

  • OwnerUserId
  • OwnerUserName

So the display algorithm would show the last editor's name if that field is NOT NULL, and show the owner's name the rest of the time.

share|improve this answer
    
@Jon Seigel, thank you. But if this were true, it would mean we should see the id of the last editor (I am talking about the home page). The last editor is not necessarily the last activity owner, whom we now see. If I answered the question I would be the last activity owner, but not the last editor. This is unless, of course, answering a question also makes you the last editor. But I do not believe this is true. I guess what I am saying is that there is a difference between the last editor and the last activity owner –  Mohamad Jun 12 '11 at 14:45
    
@Mohamad: The fields are used for both purposes, regardless of their names. When displaying the list on the homepage, it isn't necessary to differentiate between the two types of activities (although there's a feature request for that somewhere). It's a denormalized field, so the operation is very fast: to get the information from the base tables, it would be much more complicated. –  Jon Seigel Jun 12 '11 at 14:59
    
@Jon Seigel, ahhh, I see! That makes sense now. Thank you. I suppose this request can be addressed by adding a "lastActivityOwnerUserId" to the posts table, I imagine? –  Mohamad Jun 13 '11 at 13:05
    
@Mohamad: You can just use the other column and add a comment to your code for now. If the feature request I mentioned goes through, then the columns will be separated in the data dump without any further request. –  Jon Seigel Jun 13 '11 at 16:52
    
@Jon Seigel, it's interesting that. If what you say is true, it means they lose the ability to use the lastEditorUserId field to display the editor on the actual post page. They would then require a join for the question on the history table to display that info. Since last activity does not apply to answers, they can still use the lastEditorUserId field to show editors of the answers. We can infer that the question is retrieved via a separate query than its answers. I also imagine a bunch of queries to get the comments. Thank you, this was insightful. –  Mohamad Jun 14 '11 at 12:10
    
@Mohamad: Very good point, I didn't think of that. I have access to one of the real databases (not live) -- when I get home today, I will double-check to make sure I didn't miss anything. –  Jon Seigel Jun 14 '11 at 17:11
    
@Mohamad: Okay, I think I figured it out. In the real schema, there is: LastEditorUserId (viewing a question + answers), LastActivityUserId (viewing a list of questions -- this field is not included in the data dump for some reason), and LastEditorDisplayName (viewing the homepage). –  Jon Seigel Jun 14 '11 at 22:19
    
@Jon Seigel, thank you for taking a look; that clears some questions up. It's safe to assume that there is no "activity" table as I originally thought? –  Mohamad Jun 14 '11 at 22:28
    
@Mohamad: Since both questions and answers are both posts, keeping those fields denormalized works fine. When you go into the revisions list for a post, then you get data from PostHistory (which is the "activity" table). Those are only the two tables involved. –  Jon Seigel Jun 14 '11 at 22:33
    
@Jon Seigel, I understand now. I thought there was an activity table because of the activity tab in our profile pages. There, you can see activity for asked, answered, commented, accepted, awarded, and more. The history table can tell us answered and asked activities. We can get commented, accepted and awarded activities from their respective tables. But how is the data displayed chronologically when it involves different queries of different tables? Hence why an activity table made sense. This is what I originally wanted to know, to be honest. –  Mohamad Jun 14 '11 at 22:52
1  
@Mohamad: AFAIK, the activity tab is a conglomeration of data from multiple tables (i.e., Posts, Comments, Votes). –  Jon Seigel Jun 15 '11 at 0:37

I imagine it's just the UserId in the PostHistory table with the MAX(CreationDate) for that PostId.

Or perhaps for all posts with the ParentId of that post, I'm not too familiar with the data model, just inferring from the schema on Data.SE, but I'm pretty sure the PostHistory table is the key.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .