php - 需要 mysql 中多表查询的帮助

Question

我正在与 kohana 建立一个论坛。我知道已经有很好的免费论坛软件，但它是针对家庭网站的，所以我想我可以将它用作学习体验。我也没有使用 Kohana 内置的 ORM，因为我想在构建论坛的过程中了解更多有关 SQL 的知识。

对于我的论坛，我有 4 个主要表格:

• 用户
• 主题
• 帖子
• 评论

主题表:id(自动递增)，主题行。

USERS 表:用户名、电子邮件、名字和姓氏以及其他一些不相关的行

POSTS 表:id(自动递增)、post-title、post-body、topic-id、user-id、post-date、updated-date、updated-by(其中将包含最近发表评论的人的用户 ID)

COMMENTS 表:id(自动递增)、post-id、user-id 和评论

<小时/>

在论坛主页面上我希望有:

• 所有主题的列表
• 每个主题的帖子数量
• 最后更新的帖子以及更新者
• 最近更新的主题位于顶部，很可能是“ORDER BY Updated-date”

这是我到目前为止的查询:

SELECT topics.id AS topic-id, 
       topics.topic, 
       post-user.id AS user-id, 
       CONCAT_WS(' ', post-user.first-name, post-user.last-name) AS name, 
       recent-post.id AS post-id, 
       post-num.post-total, 
       recent-post.title AS post-title, 
       recent-post.update_date AS updated-date, 
       recent-post.updated-by AS updated-by
  FROM topics
  JOIN (SELECT posts.topic-id,
               COUNT(*) AS post-total                 
          FROM POSTS
         WHERE posts.topic-id = topic-id 
      GROUP BY posts.topic-id) AS post-num ON topics.id = post-num.topic-id
  JOIN (SELECT posts.* 
          FROM posts 
      ORDER BY posts.update-date DESC) AS recent-post ON topics.id = recent-post.topic-id 
  JOIN  (SELECT users.*, 
                posts.user-id 
           FROM users, posts 
          WHERE posts.user-id = users.id) as post-user ON recent-post.user_id = post-user.id 
GROUP BY topics.id

此查询几乎有效，因为它将获取具有帖子的主题的所有信息。 但它不会返回没有任何帖子的主题。

我确信该查询效率低下且错误，因为它对 posts 表进行了两个子选择，但这是我达到当前状态的唯一方法。

Answer 1

• 破折号不是 SQL 标识符中的有效字符，但您可以使用“_”代替。
• 您不必从单个 SQL 查询中获取所有内容。事实上，尝试这样做会使编码变得更加困难，有时还会使 SQL 优化器更难以执行。
• 在子查询中使用 ORDER BY 没有任何意义。
• 将您的主键列命名为 topic_id、user_id 等(而不是每个表中的“id”)，您就赢了不必在选择列表中为它们指定别名。

这是我解决这个问题的方法:

首先获取每个主题的最新帖子以及关联的用户信息:

SELECT t.topic_id, t.topic,
  u.user_id, CONCAT_WS(' ', u.first_name, u.last_name) AS full_name,
  p.post_id, p.title, p.update_date, p.updated_by
FROM topics t
INNER JOIN 
  (posts p INNER JOIN users u ON (p.updated_by = u.user_id))
  ON (t.topic_id = p.topic_id)
LEFT OUTER JOIN posts p2
  ON (p.topic_id = p2.topic_id AND p.update_date < p2.update_date)
WHERE p2.post_id IS NULL;

然后在单独的、更简单的查询中获取每个主题的帖子数。

SELECT t.topic_id, COUNT(*) AS post_total
FROM topics t LEFT OUTER JOIN posts p USING (topic_id)
GROUP BY t.topic_id;

合并应用程序中的两个数据集。