用户活动或单选查询的单独表

Question

我正在为我的客户创建一个社交网络，他们需要类似 facebook 的墙和用户最近的活动。

我有大约 8 个表，分别表示用户帐户、信息、群组、评论、页面、照片库、喜欢、分享。

在用户个人资料中，他们需要用户在网站中的整个活动。

我可以使用单个连接查询吗？或者需要有单个表作为最近活动。

Answer 1

您可以使用联合查询从不同的表中提取数据并将其存储在视图中。这是总体思路...

create view 'v_recent_activity' as 
select user_id, 'comment' as action, comment_text as action_info, comment_date as action_date from comments
union
select user_id, 'photo' as action, photo_url as action_info, photo_date as action_date from photos
union
select user_id, 'page' as action, page_url as action_info, comment_date as action_date from pages
union
select user_id, 'likes' as action, like_info as action_info, comment_date as action_date from likes
union
select user_id, 'shares' as action, share_info as action_info, share_date as action_date from shares

然后您可以使用类似

Select * from v_recent_activity where action_date between <begin_date> and <end_date>

或 的连接来访问它

Select a.*, b.* from users a join v_recent_activity b on a.id = b.user_id where a.id = <user_id> and b.action_date between <begin_date> and <end_date>

Answer 2

您可以考虑创建一个 log 或 activity 表来包含其最近活动的记录。这样您只需要一个查询。您将根据活动类型连接其他表。