您可能已经从我之前的问题中了解到,我目前正在开发一个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, 20Post_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.
更多推荐
获取特定类别的所有帖子
发布评论