php - 选择用户论坛事件，同时根据权限限制结果

Question

我的论坛分为多个表:类别、主题和话题。

CREATE TABLE forum_categories (
  cat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  cat_name VARCHAR(50) NOT NULL,

  PRIMARY KEY(cat_id),
  FOREIGN KEY (role_id)
    REFERENCES roles(role_id)
);
CREATE TABLE forum_topics (
  topic_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  cat_id INTEGER UNSIGNED NOT NULL,
  topic_name VARCHAR(50) NOT NULL,
  topic_desc VARCHAR(100) NOT NULL,

  PRIMARY KEY(topic_id),
  FOREIGN KEY (cat_id)
    REFERENCES forum_categories(cat_id)
);
CREATE TABLE forum_threads (
  thread_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  topic_id INTEGER UNSIGNED NOT NULL,
  user_id INTEGER UNSIGNED NOT NULL,
  title VARCHAR(100) NOT NULL,
  body TEXT NOT NULL,
  create_date DATETIME NOT NULL,

  PRIMARY KEY (thread_id),
  FOREIGN KEY (parent_id)
    REFERENCES forum_threads(thread_id),
  FOREIGN KEY (topic_id)
    REFERENCES forum_topics(topic_id),
  FOREIGN KEY (user_id)
    REFERENCES users(user_id)
);

类别表有一个名为 role_id 的字段，如果将其设置为 0 以外的任何值，则表示只有具有该角色的用户才可以查看该类别或与之交互该类别中的主题。

我面临的问题是当尝试提取特定用户最近的事件以供所有人查看时。我想要对包含所请求的 user_id 的线程表进行 COUNT(*)，但我需要排除具有与受限类别关联的 topic_id 的线程，除非用户请求该信息已获得许可。

如果查看特定线程，我只需提取 topic_id 并进行如下检查:

// validate topic id and check for permission
$forum = new Forum();
$valid_topics = $forum->getTopics();
if (!array_key_exists($topic_id, $valid_topics)) {
    // invalid topic id
}
$valid_categories = $forum->getCategories();
$role_id = $valid_categories[$valid_topics[$topic_id]['cat_id']]['role_id'];
if ($role_id == 0 || array_key_exists($role_id, $session_user_roles)) {
    // user has permission
}

现在我正在尝试将 PHP 逻辑转换为 SQL。这是我所追求的伪代码示例:

SELECT COUNT(*),
  (SELECT role_id,
     (SELECT cat_id
        FROM forum_topics AS t2
        WHERE topic_id = t1.topic_id) AS cat_id
     FROM forum_categories
     WHERE cat_id = t2.cat_id) AS role_id
  FROM forum_threads AS t1
  WHERE user_id = $user_id AND (role_id != 0 OR FIND_IN_SET(role_id, $session_user_roles) > 0)

请问有什么帮助吗？

Answer 1

试试这个方法

SELECT count(*)
FROM forum_threads thr
JOIN forum_topics top    ON thr.topic_id = top.topis_id 
JOIN forum_categories fc ON top.cat_id = fc.cat_id 
WHERE thr.user_id = $user_id
    AND fc.role_id IN ( 0,     24, 55, 888, .... list of user roles ... )

在用户角色列表(最后一个条件)中，始终将 0 作为第一个数字，然后是其其余角色。