创建活动日志的最有效方法

I am making a "recent activity" tab to profiles on my site and I also am going to have a log for moderators to see everything that happens on the site. This would require making an activity log of some sort.

我在我的网站上做一个“最近的活动”标签，我也会有一个日志，让版主看到网站上发生的一切。这需要做某种活动日志。

I just don't know what would be better. I have 2 options:

我只是不知道什么更好。我有两个选择:

Make a table called "activity" and then every time someone does something, add a record to it with the type of action, user id, timestamp, etc.
- Problem: table could get very long.
- 问题:桌子可能会变长。
制作一个名为“activity”的表，然后每次有人做了什么，就添加一个记录，其中包含操作类型、用户id、时间戳等。
Join all 3 tables (questions, answers, answer_comments) and then somehow show all these on the page in the order in which the action was taken.
- Problem: this would be extremely hard because I have no clue how I could make it say "John commented on an answer on Question Title Here" by just joining 3 tables.
- 问题:这将是极其困难的，因为我不知道如何使它说“约翰评论了一个关于问题标题的答案”，仅仅加入3个表。
连接所有3个表(问题、答案、应答)，然后以某种方式在页面上显示所有这些操作的顺序。问题:这将是极其困难的，因为我不知道如何使它说“约翰评论了一个关于问题标题的答案”，仅仅加入3个表。

Does anyone know of a better way of making an activity log in this situation? I am using PHP and MySQL. If this is either too inefficient or hard I will probably just forget the Recent Activity tab on profiles but I still need an activity log for moderators.

有人知道在这种情况下做活动日志的更好方法吗?我用的是PHP和MySQL。如果这太低效或者太困难，我可能会忘记配置文件上的近期活动选项卡，但是我仍然需要一个活动日志作为版主。

Here's some SQL that I started making for option 2, but this would not work because there is no way of detecting whether the action is a comment, question, or answer when I echo the info in a while loop:

下面是我开始为选项2做的一些SQL，但这不会起作用，因为在while循环中，当我回传信息时，无法检测该操作是否为注释、问题或答案:

SELECT q.*, a.*, ac.* 
    FROM questions q JOIN answers a ON a.questionid = q.qid
        JOIN answer_comments ac ON c.answerid = a.ans_id
WHERE q.user = $userid 
AND a.userid = $userid 
AND ac.userid = $userid
    ORDER BY q.created DESC, a.created DESC, ac.created DESC

Thanks in advance for any help!

感谢您的帮助!

2 个解决方案

#1

Why do you have q.user and q.userid?

为什么有q。用户和q.userid吗?

For option 2 (the better option IMO - as long as you've indexed properly), I think a UNION is more what you're looking for. Something like this:

对于第二种选择(在我看来更好的选择——只要你索引正确)，我认为你更需要一个联盟。是这样的:

SELECT 'question' AS action, id, created 
    FROM questions WHERE userid = {$userid}
    UNION
SELECT 'answer' AS action, id, created
    FROM answers WHERE userid = {$userid}
    UNION
SELECT 'comment' AS action, id, created
    FROM answer_comments WHERE userid = {$userid}
ORDER BY created DESC LIMIT 20

The 'question' / 'answer' / 'comment' tells you which action was taken. Possible issues you may run into: being a UNION, each SELECT statement must have the same number of columns, so if one is short, you can just add a NULL e.g.:

“问题”/“回答”/“评论”告诉你采取了什么行动。您可能遇到的问题:作为一个联合，每个SELECT语句必须有相同数量的列，因此如果一个列是短的，您可以添加一个NULL，例如:

SELECT 'comment', id, created, NULL FROM ac

Also, if one of the created columns has a different name you can just alias

另外，如果一个创建的列有一个不同的名称，您可以使用别名。

SELECT 'comment', id, comment_date AS created FROM ac

#2

I like "option 2", you would basically be duplicating your data and it would slow things down a bit with the extra reads/writes. Maybe instead of doing a

我喜欢“选项2”，你基本上会复制你的数据，它会用额外的读/写来降低一些速度。也许不是做a

SELECT q.*, a.*, ac.*

You could either just get the data that you need from each table, or a slightly cleaner way may be to do a Union of the three tables, after limiting your query to only those posts by selected user, and order by the date posted.

您可以从每个表中获取所需的数据，也可以使用一种稍微干净一点的方法，将三个表合并在一起，然后将查询限制为选定的用户只对这些文章进行查询，并按发布日期进行排序。

#1