如何使用mysql数据库创建阻止系统? [英] How do i create a blocking system with mysql database?

问题描述

我创建了一个社交网站，其中包含三个简单的表格，如下所示.

I've created a little social website which has three simple tables as show below.

Table User
id (pk)  | user_name
1       | a
2       | b
3       | c

Table Post
id (pk)  | user_id (fk)
1       | 1
2       | 3
3       | 2
4       | 2

Table Block
id (pk) | block_by (fk)  | blocked_to (fk)
1       | 1              | 2

该用户可以屏蔽他们想要的任何人，并且如果发生屏蔽，两个用户将不再在他们的新供稿源上看到彼此的帖子.

在我的表中，这表示user1和user2不会看到彼此的帖子，但是'user3'通常会看到每个人的帖子，因为他不属于该表格块.

The user can block anyone they want, and if blocking happen, two users will not longer see each other posts on their newfeeds.

From my table that mean user1 and user2 will not see each other posts but 'user3' will normally see everyone' post because he not belong to the table block.

    select * from Post p 
where p.user_id not in 
(select b.blocked_to from Block b  
where b.block_by = '$_SESSION[userid]')

从SQL查询中，user1无法看到user2的帖子，但是user2仍然可以看到user1的帖子，这是我不希望的.我可以针对这种情况的最佳查询是什么? 还请考虑我的数据库设计是这条好路吗?您可以建议我，非常感谢:)

From SQL query user1 cannot see user2's post, but user2 still see user1 post, this is i don't want. What is the best query for this situation that i can do? Please also consider my database design is this good way to go? You can suggest me, Many thanks:)

推荐答案

我会尝试联接Post和Block表，以便过滤出给定用户(通过会话获取)所请求的帖子.给其他可能被阻止的用户:

I would try joining the Post and Block tables in order to filter out posts requested by a given user (which you obtain through the session) belonging to other potentially blocked users:

SELECT * FROM Post p INNER JOIN Block b
ON p.user_id = b.blocked_by
WHERE b.blocked_to <> '$_SESSION[userid]'

我还将对您的架构进行一些修改，以在Block表中同时包含两个 关系:

I would also modify your schema slightly to include both relationships in the Block table:

+---------+---------------+-----------------+
| id (pk) | block_by (fk) | blocked_to (fk) |
+---------+---------------+-----------------+
|   1     |      1        |       2         |
|   2     |      2        |       1         |
+---------+---------------+-----------------+

首先，我认为这是代表双向关系的最简单方法.最糟糕的是，Block表的大小将增加一倍.此外，您不知道将来是否会遇到以块为一种方式的情况.例如，您可以允许用户1阻止用户2，其中用户1仍可以阅读用户2的帖子，反之亦然.

First of all, I believe this is the easiest way to represent a two-way relationship. At worst, the Block table would double in size. Furthermore, you don't know if at some point in the future you might have a scenario where a block is one way. For example, you might allow User 1 to block User 2, where User 1 can still read User 2 posts but not vice versa.

这篇关于如何使用mysql数据库创建阻止系统?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！