如何使用 MySQL 对新闻提要中出现的帖子应用隐私设置？

Question

我想要实现的是一个复杂的隐私例程，我有下表。

posts

posts_privacy

Privacy_global

followerlist

我们的网站上有一个新闻提要之类的东西，它从用户关注的人那里获取 10 个最新帖子，有 3 个级别的隐私，第一个是 Privacy_global，用户可以在其中设置隐私选项posts 、 0 、 1 和 2 分别表示“只有我”、“所有人”、“仅限关注者”，然后是 posts_privacy，用户可以在其中选择列出他想要向其显示的人或在发布帖子之前隐藏帖子，这如果设置的话，设置会覆盖privacy_global设置和posts_privacy。

现在我的问题是，当我使用 php 在新闻提要中应用隐私时，我基本上会要求这样的帖子。

SELECT `post`.*,
  `users`.`fullname`,
  `users`.`username`,
  `posts_privacy`.`hide`,
  `posts_privacy`.`show`,
FROM `post`
  LEFT JOIN `posts_privacy` ON `post`.`id`=`posts_privacy`.`postid`
  INNER JOIN `users` ON `post`.`userid` = `users`.`id`
WHERE (`post`.`userid` IN (1,2,3,4,5,6) 
AND 12 NOT IN (
  SELECT `hide` FROM `posts_privacy` WHERE `postid`=`post`.`id`)
  OR `show`= 12
)

 GROUP BY `post`.`id`
 ORDER BY `time` DESC LIMIT 10"

我不知道如何应用所有其他条件，在此之前，我使用另一个查询并返回帖子需要隐藏或显示的人员的序列化数组，然后在 php 中对其进行反序列化和处理，并从数组中删除该帖子在发送到浏览器之前，这在新闻源中创建了一个漏洞，而不是加载 10 个帖子，只出现 6 个帖子，因为 php 在发送到浏览器之前删除了 4 个具有隐私的帖子，我的目标是做所有的事情在帖子离开数据库之前 SQL 中的隐私条件，所以我在新闻源中的帖子计数方面没有这个问题。

我尝试研究 MySQL 函数和过程，但被告知它们无法处理查询或返回表。

关于如何解决这个问题有什么建议吗？

编辑：如果有帮助的话，我正在尝试制作的系统与 Facebook 上的系统类似，用户可以在其中设置特定的隐私，但也有全局设置，完成后会被覆盖。

---

表结构：

followerslist：

id PRIMARY KEY BIGINT(20)
用户 ID BIGINT(20) <<单击关注按钮的用户 ID
targetid BIGINT(20) <<被关注的用户的 id

帖子：

id PRIMARY KEY BIGINT(20)
内容文本
标题 VARCHAR(100)
时间 DATETIME
用户 ID BIGINT(20)

posts_privacy:

id 主键 BIGINT(20)
postid BIGINT(20)
隐藏 BIGINT(20)
显示 BIGINT(20)

Answer 1

以下是获得您想要的结果的一种方法。不过，您需要进行一些仔细的测试，因为我已经没有时间进行进一步的测试了。它基于查看者的视角 - 请求他们有权查看的帖子的用户。它并不完美，所以请将其视为构建的基础。

查询返回的某些字段在最终版本中将不需要，但它们对于测试很方便：

SET @viewerid := 5;
SELECT
  p.id AS post_id,
  owner.id AS owner_id,
  pg.privacy AS privacy_global,
  pp.hide AS hide_from,
  pp.show AS show_to,
  f.userid AS viewer,
  f.targetid AS following
FROM posts AS p
JOIN users AS owner ON owner.id = p.userid
JOIN privacy_global AS pg ON pg.postid = p.id
LEFT JOIN posts_privacy AS pp ON pp.postid = p.id
LEFT JOIN followerlist AS f ON f.userid = @viewerid
WHERE ((pg.privacy = 0 AND pp.show = @viewerid)
OR (pg.privacy = 1)
OR (pg.privacy = 2 AND f.targetid = p.userid))
AND ((pp.hide != @viewerid OR pp.hide IS NULL)
AND (p.userid != @viewerid))
GROUP BY p.id
LIMIT 10;

在测试期间，您可能会发现分解查询很有用。以下块列出了帖子 ID、帖子所有者、全局隐私设置以及隐藏/显示用户 ID：

SELECT
  p.id AS post_id,
  owner.id AS owner_id,
  pg.privacy AS privacy_global,
  pp.hide AS hide_from,
  pp.show AS show_to
FROM posts AS p
JOIN users AS owner ON owner.id = p.userid
JOIN privacy_global AS pg ON pg.postid = p.id
LEFT JOIN posts_privacy AS pp ON pp.postid = p.id

添加查看者用户 ID，并且将为每个帖子显示额外的行，其中该帖子的所有者后面跟着查看器：

SET @viewerid := 1;
SELECT
  p.id AS post_id,
  owner.id AS owner_id,
  pg.privacy AS privacy_global,
  pp.hide AS hide_from,
  pp.show AS show_to,
  f.userid AS viewer,
  f.targetid AS following
FROM posts AS p
JOIN users AS owner ON owner.id = p.userid
JOIN privacy_global AS pg ON pg.postid = p.id
LEFT JOIN posts_privacy AS pp ON pp.postid = p.id
LEFT JOIN followerlist AS f ON f.userid = @viewerid;

然后，WHERE 子句继续根据每个帖子和全局隐私设置来缩小这些结果的范围。