Adding a date field to qa_uservotes

Question

I suggest to add a date field to the table qa_uservotes, so that each vote can be chronologically sorted.

Current structure of table qa_uservotes is:

postid	int(10)
userid	int(10)
vote	tinyint(4)
flag	tinyint(4)

 

If we had a date field, we could easily implement something nice like stackoverfow. What votes at what time:

Update: This should not represent a define history of votes but the votes at their last occurence (see pupi1985's answer for details).

 

Please vote-up this question if you want this :)

Thanks,
Kai

Comments

+1 upvote from my side .

Answer 1

The thing is that adding the field will not allow you to create the graph. I absolutely love that graph... IMO, that would be the first feature I would work on immediately after performing some refactorings in the core (eg: removing all includes).

 

Now, the graph shows the history of all the reputation changes. Knowing the date of a vote will not be enough to know the history nor all of the reputation changes. In order to be able to create a real graph you will need to know when each reaputation change was performed, including, but not limited to, user up/down/null votes, answer selections, admin manually assigning points to a user, etc.

 

So each of these events will have to be tracked and kept in the history. Adding a date field to a vote will not allow you to see the vote history, only the last vote (could be an edited vote) you performed on that question. History should be read only, so if you change 3 times your vote you should have 3 votes recorded. That way you will be able to create that graph.

 

I'd say you have all you need in the event logger plugin. I would make a list of all the possible events that can fire a point change and then see if they are actually provided by that plugin. If something is missing, adding it to the params of each event would have a smaller impact that adding more fields, IMO.

 

PS: I would upvote the feature but I would downvote the proposed solution. So null for now :)

Comments

Thanks for your reply. I see now that the idea/graphic I used from stackoverflow is misleading. I actually wanted no define history but just a "list all votes" graph.

I absolutely agree with the eventlogger solution you suppose. However, my personal problem would be that I empty the eventlog table each quarter to keep the database performant... ;)

---

OK. So if you just want the snapshot of the current up/down votes (note in this case null votes are meaningless) then adding that field would solve the issue. However, IMO, that feature is not so useful (compared to fully tracking history, as SO does). Anyway, in that case you don't need the event logger; you just need to catch all the up/down events with a plugin and then store them in a separate table that references the vote and then just join them. Of course, in your table, you can have all the metadata you need.

Obviously, you will have to deal with missing data, which I would suggest you to default to a valid date (most likely the moment in which the plugin creates the table).

---

I am writing a plugin today to list the last x votes. Problem I face now,it cannot be done chronologic as there is no timestamp on each vote (q2a v1.6.3, table qa_uservotes).

Now I need to do unnecessary extra work, i.e. take the last x posts (Q,A) and check them for votes, then list them. Plus, votes that are made on old posts are "invisible" to me now!

I am quite certain that we need a timestamp for each vote **in core**. Do you think you can write a core addition and do a pull request?

---

This seems to be a low complexity change. However, it must be taken into account that there should be added 2 timestamps: one for the votes and one for the flags. Having the timestamps without an index on them is not an option as searches on that field will take ages. So you need to add 2 new indexes on the ^uservotes table. This means updating 2 more indexes when performing a vote or flag. IMO, voting is already slow (since you click the up/down vote arrow until you get the vote feedback) and this will only make it slower. That is particularly why I don't like this idea.

Anyway, it doesn't really matter whether I like it or not as I have no control over the core. That is Scott's job; he decides what should and what shouldn't be there. So even if I create a pull request for this, it would be useless if Scott doesn't like it. I believe the first step would be to contact Scott and let him know about the issue and the proposed solution. Then he will decide whether this should get to the core or not.

---

I PNed Scott.

For the "flags". I am still not sure if we need this column anyways. I checked 12410 votes in my biggest forum, all flag fields have the default value "0"!

---

There isn't really a "need" of any index when it comes to the core. In order to satisfy the plugin's requirements you only need an indexed timestamp updated when voting. The flag is not necessary for this plugin. I'm just thinking in general (core) terms and what might be useful for the end user or developers.

Now, the only feature I can think of in which this information might be useful is to display the date of the vote action when hovering over an up/down arrow. This could be also applied to the flag (regardless of the fact that it will be used by the plugin or not).

Anyway, to show the timestamp of the vote/flag the index is not necessary so I wouldn't add an index to the core (even if it is only on the vote timestamp). Not to mention that, in my previous comment, I was thinking on an index just on the timestamp, which means you can quickly get all the ordered votes site-wide but not at a user level, EG: Last 10 votes per user. That would require an even more custom index on userid and timestamp (on that order). And, again, I wouldn't add that to the core either.

To sum up, adding a timestamp might help and will allow the end user to see when a vote (or flag, if the other timestamp is also added) has been performed. It will also allow your plugin to search on that column and order the way you want. However, without the appropriate index, the query you need in your plugin will run very slow as it will need to perform a full scan. IMO, the best core-friendly approach to implement this plugin will be to create a custom event logger and track the votes manually with any information you need on a separate table.

---

A timestamp will also help to prevent spam votes better. I ran into spam votings again, with a plugin I could display the last 10 votes of this user in his profile and everybody could see if he is spam-voting. Hope this comes in q2a v1.7, quite urgent matter IMO!