MySQL 加入好友动态
我目前正在记录用户的所有操作，并希望向关注他们的人显示他们的操作 - 有点像 Facebook 为朋友做的那样。
我将所有这些操作记录在具有以下结构的表中：
- id - PK
- userid - 其操作被记录的用户的 ID
- actiondate - 操作发生时
- actiontypeid - 操作类型的 ID（存储在不同表中的操作类型 - 即关注其他用户、在人们的个人资料上书写、创建新内容、评论现有内容等）
- objectid - 他们刚刚创建的对象的ID（即评论ID）
- onobjectid - 他们执行操作的对象的ID（即他们评论的内容的ID）
现在的问题是有多种类型的操作被记录（actiontypeid）。
检索数据以显示给用户的最佳方式是什么？
最简单的方法是闲聊用户关注的数据集，然后从那里获取其他表中的所有其他信息（即您所关注的人刚刚开始关注的用户的名称、用户个人资料的名称）他们写了等等）。然而，这会在 while 循环中创建大量的小型查询和数据库访问。这不是一个好主意。
我可以使用联接来检索一个海量数据集中的所有内容，但我如何知道在一个查询中从哪里获取数据呢？ - 有不同类型的操作需要我根据actiontypeid查看多个不同的表来检索数据...
即要获取用户 X 现在正在关注用户 Y< /em>我必须从关注者表中获取我的数据（用户 Y 的用户名），而用户 X 对内容 Y 发表了评论< /strong> 需要我查看 content 表以获取内容的标题和 URL。
欢迎任何建议，谢谢！
I'm currently logging all actions of users and want to display their actions for the people following them to see - kind of like Facebook does it for friends.
I'm logging all these actions in a table with the following structure:
- id - PK
- userid - id of the user whose action gets logged
- actiondate - when the action happened
- actiontypeid - id of the type of action (actiontypes stored in a different table - i.e. following other users, writing on people's profiles, creating new content, commenting on existing content, etc.)
- objectid - id of the object they just created (i.e. comment id)
- onobjectid - id of the object they did the action to (i.e. id of the content that they commented on)
Now the problem is there are several types of actions that get logged (actiontypeid).
What would be the best way of retrieving the data to display to the user?
The easiest way out would be gabbing the people the user follows dataset and then just go from there and grab all other info from the other tables (i.e. the names of the users the people you're following just started following, names of the user profiles they wrote on, etc.). This however would create a a huge amount of small queries and trips to the database in a while loop. Not a good idea.
I could use joins to retrieve everything in one massive data set, but how would I know where to grab the data from in just one query? - there's different types of actions that require me to look into several different tables to retrieve data, based on the actiontypeid...
i.e. To get User X is now following User Y I'd have to get my data (User Y's username) from the followers table, whereas User X commented on content Y would need me to look in the content table to get the content's title and URL.
Any tips are welcome, thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
考虑为不同的actiontypeid 创建多个视图。将它们联合起来拥有完整的历史。
Consider creating several views for different actiontypeids. Union them to have one full history.