php - 加入查询 : How to return results from join table2 as an array in the table1

Question

我需要显示一个文章列表，其中包含每篇文章对应的全套标签。我试着做这个查询:

SELECT articles.article_id, `title`, `text`, tags.tag_name 
FROM `articles` 
LEFT JOIN `tags` 
on articles.article_id = tags.article_id 
WHERE articles.author_id = 150342

但是，该查询为每个标签返回表 articles 中的同一行，次数与标签的数量一样多。

像这样:

Array
(
  [0] => Array
  (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag1
  )
 [1] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag2
 )
 [2] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag3
 )
)

有没有办法在数组中为每篇文章返回 tag_name？看起来像这样:

Array
(
 [0] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => array ([0] => tag1, [1] => tag2, [2] => tag3)
 )
)

Answer 1

不是数组，但你可以得到逗号分隔的字符串，

SELECT articles.article_id, `title`, `text`, GROUP_CONCAT(tags.tag_name )as tg_name
FROM `articles` 
LEFT JOIN `tags` 
on articles.article_id = tags.article_id 
WHERE articles.author_id = 150342
GROUP BY tags.article_id