mysql - 用户事件或单选查询的单独表

Question

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

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

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

我可以使用单个加入查询吗？还是需要一个单个表作为最近事件。

Answer 1

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

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>