与包含标记的另一个表连接

I have a table called "posts" with the following struture:

我有一张叫做“posts”的桌子，上面有如下结构:

post_id  |  post_body  | post_posterid

And I have another table called "tags":

我还有一个表格叫做"标签"

tag_id  |  tag_name

A third table stores the relation between the two tables (will have multiple tags_id for each post_id as separate records):

第三个表存储两个表之间的关系(每个post_id都有多个tags_id作为单独的记录):

post_id  |  tag_id

This is similar to *'s tagging that i am trying to create.

这类似于我正在尝试创建的*的标记。

But I do not know how to join the tables and to concatenate tags for each post, in a single record. Like this:

但是我不知道如何在一条记录中为每个帖子连接表和连接标签。是这样的:

post_id  |  post_body  |  post_postername  |  tag_id1  |  tag_id2  |  tag_id3  ..... tag_name1  |  tag_name2  | tag_name3 ....

So while fetching from mysql using PDO, I can avoid further queries to get tag names.

因此，在使用PDO从mysql获取数据时，我可以避免进一步查询以获取标记名。

EDIT More details:

编辑详细信息:

First table("posts") contains post id(a unique id), posted message and poster's id(unique poster id which is stored in table "poster"). Second table "tags" contains possible tags(like "php", "vb.net", etc. like in *) with unique tag_id. Third table contains the relation between posted message and tags. So it contains post_id and its corresponding tag_id. It is possible to have more than 1 tag assigned to same post.

第一个表(“posts”)包含post id(一个惟一id)、post消息和poster id(存储在table“poster”中的惟一poster id)。第二个表“标记”包含可能的标记(如“php”、“vb.net”等，如*)，具有唯一的tag_id。第三个表包含已发布消息和标记之间的关系。它包含post_id和相应的tag_id。有可能有超过一个标签被分配到相同的岗位。

What i want: when queried, i can join "posts" with "posters" table to get name. But i want to get tags for each posts also.

我想要的:当被问到的时候，我可以加入“贴子”和“海报”表格来得到名字。但我也想给每个帖子都贴上标签。

GROUP_CONCAT() is answer for my question. But how to obtain it as tag_id - tag_name pair, instead of all tag_id in a field and all tag_name in another.

GROUP_CONCAT()是我的问题的答案。但是如何获得它作为tag_id - tag_name对，而不是字段中的所有tag_id和另一个字段中的所有tag_name。

Eg: 1-VB , 5-PHP , 19-SQL

1-VB, 5-PHP, 19-SQL

I will separate this pairs in PHP

我将在PHP中分离这些对。

What i am trying is same as *'s tagging system.

我所尝试的和*的标签系统一样。

1 个解决方案

#1

keyword is GROUP_CONCAT

关键字是GROUP_CONCAT

and query is like

和查询就像

SELECT 
posts.*,
GROUP_CONCAT(tags.tag_name) tags
FROM
posts
LEFT JOIN relation ON relation.post_id  = posts.id
LEFT JOIN tags ON tags.tag_id = relation.tag_id
GROUP BY posts.id

hope this help

希望这有助于

#1