php - 获取用户的所有帖子及其类别

Question

现状

我目前正在开发一个博客系统，我一直在尝试获取特定用户发布的所有帖子及其类别。

查询只显示数据库中的第一篇文章，不管用户有多少篇文章。此外，生成的类别输出是错误的。

数据库

以下是创建所需的三个表的 SQL 命令。

发布

create table Post(
    headline varchar(100),
    date datetime,
    content text,
    author int unsigned,
    public tinyint,
    type int,
    ID serial,
    Primary Key (ID),
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

author 是创建帖子的用户的 ID，public 确定帖子是可以被所有人阅读还是只是草稿和 type 确定它是博客文章 (0) 还是其他内容。

类别

create table Kategorie(
    name varchar(30),
    short varchar(200),
    ID serial,
    Primary Key (name)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Post_Kategorie

create table Post_Kategorie(
    post_ID bigint unsigned,
    kategorie_ID bigint unsigned,
    Primary Key (post_ID, kategorie_ID),
    Foreign Key (post_ID) references Post(ID),
    Foreign Key (kategorie_ID) references Kategorie(ID)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

查询

这是我目前的查询，但如上所述，它并没有像我想要的那样工作。

SELECT Post.headline, Post.date, 
 CONCAT(
  "[", GROUP_CONCAT('{"name":"',Kategorie.name,'","id":',Kategorie.ID,'}'), "]"
 ) as "categorys"
FROM Post, Kategorie, Post_Kategorie
WHERE Post.author = 1
AND(
  Post.public = 1
  AND Post.type = 0
)AND(
  Post_Kategorie.post_ID = Post.ID
  AND Post_Kategorie.kategorie_ID = Kategorie.ID
)

由于一篇文章可以有多个类别，查询会生成 JSON，然后在 PHP 中将其解码为一个对象。认为这是存档的简单方法。

我在这里错过了什么？

Answer 1

你最后错过了这个:

GROUP BY Post.headline, Post.date

更新

此外，您还注意到，当帖子没有类别时，它不会出现。问题是您正在通过 WHERE 子句执行 JOIN 。当您这样做时，您的连接将始终表现为 INNER JOIN。

为了始终拥有帖子，您需要LEFT JOIN:

SELECT Post.headline, Post.date, 
 CONCAT(
  "[", GROUP_CONCAT('{"name":"',Kategorie.name,'","id":',Kategorie.ID,'}'), "]"
 ) as "categorys"
FROM Post 
  LEFT JOIN Post_Kategorie 
    ON Post.ID = Post_Kategorie.post_ID
  LEFT JOIN Kategorie 
    ON Post_Kategorie.kategorie_ID = Kategorie.ID
WHERE Post.author = 1
AND Post.public = 1
AND Post.type = 0
GROUP BY Post.headline, Post.date