MySQL 如何组合这些表? [英] MySQL how do I combine these tables?

问题描述

我有三张桌子:

blog_posts
  int id (auto increment)
  varchar(255) title
  text content

blog_tags
  int id (auto increment)
  varchar(63) name

blog_posttags
  int id (auto increment)
  int post_id
  int tag_id

如何像具有以下结构的多维数组一样获取(最好使用尽可能少的查询)这些数据?

How can I get (preferably with as few as possibly queries) this data like a multidimensional array with the following structure?

我可以弄清楚如何从数据库中创建数组，但不知道如何在不查询我收到的每篇博文以查看哪些标签属于它的情况下完成它.

I can figure out how to make the array from the database, but not how to get it done like this without making a query for every blogpost I receive to see which tags belong to it.

$blogposts = array(
    array(
        'id' => 0,
        'title' => 'blogpost title',
        'content' => 'blogpost content',
        'tags' => array(
            'tagname1', 'tagname2', 'tagname3', ...,
        )
    ),
    ... (multiple blogposts because WHERE condition may return more than 1)
)

我想我必须使用 UNION 或 JOIN 或类似的东西，但我对高级 MySQL 语句并没有真正的经验.

I'd imagine I have to use UNION or JOIN or something like that, but I'm not really experienced with advanced MySQL statements.

您可以假设 blog_posttags 中的 tag_id 也存在于 blog_tags 中，post_id 也是如此和 blog_posts.

You may assume that a tag_id in blog_posttags also exists in blog_tags, same goes with post_id and blog_posts.

推荐答案

您可以使用 MySQL 的 GROUP_CONCAT 函数:

You can get all the tags in a single query using MySQL's GROUP_CONCAT function:

SELECT   a.id
        ,a.title
        ,a.content
        ,(      SELECT  GROUP_CONCAT(b.name) 
                FROM    blog_tags b
                JOIN    blog_posttags c
                ON      b.id = c.tag_id
                WHERE   c.post_id = a.id
         ) AS tags
FROM    blog_posts a

将连接标签，如:

id  title       content         tags
-------------------------------------------
1   Blog 1      blah blah blah  funny,work 
2   Next Post   yadda yadda     school,work

它返回一个这样的数组:

which returns an array like this:

$blogposts = array(
    array(
        'id' => 0,
        'title' => 'blogpost title',
        'content' => 'blogpost content',
        'tags' => 'tagname1,tagname2,tagname3'
    ),
//etc
);

然后你只需要像这样运行一个循环来拆分逗号分隔的标签字符串

And then you just need to run a loop like this to split up the comma delimited tag string

foreach($blogposts as $k => $v){
    $blogposts[$k]['tags'] = explode(',', $v['tags']);
}

这是示例 SQL Fiddle，其中包含我用来测试的几个虚拟标签和帖子发帖前这个

Here is sample SQL Fiddle with a couple dummy tags and posts I used to test this before posting

编辑

这是一种无需使用子查询即可获得相同结果的替代方法:

Here is an alternate way to achieve the same results without using a sub-query:

SELECT       a.id
            ,a.title
            ,a.content
            ,GROUP_CONCAT(c.name) AS tags
FROM        blog_posts a
JOIN        blog_posttags b
ON          a.id = b.post_id
JOIN        blog_tags c
ON          b.tag_id = c.id
GROUP BY    a.id

这篇关于MySQL 如何组合这些表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！