0

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?

Improve this question
1
  • @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
    Commented Jun 12, 2011 at 14:42

2 Answers 2

Reset to default
2

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.

Improve this answer
11
  • @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
    Commented Jun 12, 2011 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
    Commented Jun 12, 2011 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
    Commented Jun 13, 2011 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
    Commented Jun 13, 2011 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
    Commented Jun 14, 2011 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
    Commented Jun 14, 2011 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
    Commented Jun 14, 2011 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
    Commented Jun 14, 2011 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
    Commented Jun 14, 2011 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
    Commented Jun 14, 2011 at 22:52
  • 1
    @Mohamad: AFAIK, the activity tab is a conglomeration of data from multiple tables (i.e., Posts, Comments, Votes).
    – Jon Seigel
    Commented Jun 15, 2011 at 0:37
1

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.

Improve this answer

You must log in to answer this question.

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