获取特定类别的所有帖子 [英] Get all posts from a specific category

问题描述

您可能已经从我之前的问题中了解到，我目前正在开发一个Blog系统.

As some of you might already know from my previous questions, I'm currently developing a Blog-system.

这一次，我被困在获取特定类别的所有帖子及其类别.

This time, I'm stuck at getting all posts from a specific category, with their category.

以下是创建三个必需表的SQL命令.

Here are the SQL-commands to create the three required tables.

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)或其他的东西.

author is the ID of the user who created the post, public determines if the post can be read from everyone or is just a draft and type determines if it's a blog-post (0) or something else.

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;

查询

这是我当前的查询，用于获取所有带有特定类别标记的帖子，该类别由类别的ID确定:

The Query

This is my current query to get all posts tagged with a specific category, which is determined by the category's ID:

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

该查询可用于列出所有带有特定类别标记的帖子，但是categorys列会混合使用，因为每个列出的帖子都具有所有可用类别(Kategorie-表中列出的每个类别).

The query works for listing all posts tagged with a specific category, but the categorys-column gets mixed up as every listed post has all available category's (every category listed in the Kategorie-table).

我确定问题出在INNER JOIN条件下​​，但是我不知道在哪里.请指出正确的方向.

I'm sure the problem lays in the INNER JOIN-condition, but I have no clue where. Please point me in the right direction.

推荐答案

我怀疑您的CONCAT函数可能存在问题，因为它混合了不同类型的引号.我认为"["和"]"应该分别是'['和']'.

I suspect there might be issues with your CONCAT function, as it mixes different types of quotation marks. I think "[" and "]" should be respectively '[' and ']'.

否则，问题似乎出在联接之一上.特别是INNER JOIN Kategorie没有指定连接条件，我认为应该是Post_Kategorie.Kategorie_ID = Kategorie.ID.

Otherwise, the problem does seem to be with one of the joins. In particular, INNER JOIN Kategorie does not specify the joining condition, which, I think, should be Post_Kategorie.Kategorie_ID = Kategorie.ID.

因此整个查询应该是这样的:

There entire query should thus be something like this:

SELECT Post.headline, Post.date, Post.ID,
  CONCAT(
    "[", GROUP_CONCAT('{"name":"',Kategorie.name,'","id":',Kategorie.ID,'}'), "]"
  ) as "categorys"
FROM Post
INNER JOIN Post_Kategorie
  ON Post.ID = Post_Kategorie.post_ID
INNER JOIN Kategorie
  ON Post_Kategorie.Kategorie_ID = Kategorie.ID
WHERE Post.public = 1
  AND Post.type = 0
GROUP BY Post.headline, Post.date
HAVING MAX(CASE Post_Kategorie.kategorie_ID WHEN 2 THEN 1 ELSE 0 END) = 1
ORDER BY Post.date DESC
LIMIT 0, 20

Post_Kategorie.kategorie_ID = 2条件已被修改为CASE表达式，并移至了HAVING子句，它与MAX()聚合函数一起使用.其工作原理如下:

The Post_Kategorie.kategorie_ID = 2 condition has been modified to a CASE expression and moved to the HAVING clause, and it is used together with the MAX() aggregate function. This works as follows:

• 如果一个帖子被一个或多个属于Kategorie.ID = 2的标签标记，则CASE表达式将返回1，MAX的求值也将为1.因此，所有组都将有效并保留在输出中.

• If a post is tagged with a tag or tags belonging to Kategorie.ID = 2, the CASE expression will return 1, and MAX will evaluate to 1 too. Consequently, all the group will be valid and remain in the output.

如果没有标记过帖子的标签属于上述类别，则CASE表达式将永远不会等于1，MAX也不会.结果，整个组将被丢弃.

If no tag the post is tagged with belongs to the said category, the CASE expression will never evaluate to 1, nor will MAX. As a result, the entire group will be discarded.

这篇关于获取特定类别的所有帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！