I want to post about the filter I developed for the cat people poll
. Here are the unfiltered results
When I asked, MSO's requirements for the filter were:
1 hour of play time (time spent in living job role) in the 30 days leading up to the vote being created (by summing the role_time_log table for the time period)
...and the query below was submitted for his approval before it was posted publicly.
Here's the raw SQL query that I came up with:
Code: Select all
count(DISTINCT tbl_poll_vote.id) AS votes,
tbl_poll_option.text AS `option`
LEFT JOIN tbl_poll_option ON tbl_poll_vote.optionid = tbl_poll_option.id
LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')
LEFT JOIN tbl_poll_question ON tbl_poll_vote.pollid = tbl_poll_question.id
WHERE tbl_poll_vote.pollid = ?
AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime
GROUP BY tbl_poll_vote.optionid
HAVING sum(tbl_role_time_log.delta) >= 60
ORDER BY votes DESC;
First off, every instance of `tbl_` is replaced with the actual table prefix used by the database. This allows statbus to be used regardless of what database prefix is being used.
Every vote cast gets a unique, automatically incrementing ID associated with it. This allows the database to keep track of everything, and speeds up queries. This is a standard practice across relational databases. Everything in tgdb polls is referenced by an ID. `count(DISTINCT tbl_poll_vote.id) AS votes` simply tallies up the number of unique IDs and provides it in the result as a column titled 'votes'.
In tgdb, polls are split across a few different tables:
- `ss13poll_question` contains the basic information about the poll, like what the question is, who started the poll, how long it runs for, etc.
- `ss13poll_option` are the options associated with the poll. So answers like 'yes', 'no', 'atlantaned is the best admin' are stored in this table. These answers are associated with, and tied to, the ID column from `ss13poll_question`.
- `ss13poll_vote` is a record of the actual vote cast by a player. This records the poll ID, and the ID of the vote option that the player selected. The player's ckey is also saved as well.
It seems crazy that this complex query only returns two columns, the vote option text and how many votes were cast for it. The actual filtering part starts with `LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')`:
This line takes the ckey that gets saved when a player casts a vote, and references it with EVERY entry they have in `ss13role_time_log`. This is a really useful table that stores how long someone spent as a role, saved every 10 minutes. Some 'broader' data is also stored in `ss13role_time_log`, such as how long someone spends living (not as a ghost). So if I spend ten minutes playing captain, I also spent ten minutes living. Make sense?
So we're only collecting how much time a player has spent living when we join `ss13role_time_log`. The second part of the filter is this line: `AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime`
`ss13role_time_log` also includes a timestamp of when the data was recorded. This line above says "only look at entries for this ckey in `ss13role_time_log` that were entered within 30 days before the start of the poll". So we're only looking at time spent playing during that period.
`WHERE tbl_poll_vote.pollid = ?` simply selects the poll data based on the poll ID. The `?` is a placeholder for a the poll ID, the replacement/securing of which is handled by PHP.
Without `GROUP BY tbl_poll_vote.optionid`, we'd get a result of almost 600 rows. This line tells the query to count based on votes that share the same optionid.
Finally, the last part of the filter, `HAVING sum(tbl_role_time_log.delta) >= 60`. This lets me use an aggregate function, `sum()`, to further restrict the query. In this case, we're taking all those minutes we found from `ss13role_time_log` and summing them up. And if the sum is greater than or equal to 60 (one hour of playtime within 30 days of the poll starting), we count it!
Lastly, `ORDER BY votes DESC;` simply orders the results by the `votes` column, in descending order.
I hope this has been illuminating for you.If there are any questions feel free to ask them here, or ping me on discord.