SQL:重构GROUP_CONCAT查询 [英] SQL: Refactoring a GROUP_CONCAT query

问题描述

我最终的目标是生成代码 博客文章链接，标签 与帖子有关.

My eventual goal is to generate tag links for blog posts, the tags relating to the post.

当前我的查询中有一个GROUP_CONCAT，它抓住相关标签，并且返回的tags列值类似于:

Currently my query has a GROUP_CONCAT which grabs relating tags and the returning tags column value is something like:

布鲁克林，纽约曼哈顿"

'Brooklyn, Manhattan, New York'

来自

SELECT 
post.id, 
post.name, 
GROUP_CONCAT( tags.name order by tags.name ) AS tags 
FROM post 
LEFT JOIN tags_items
  ON post.id = tags_items.post
LEFT JOIN tags
  ON tags_items.tag = tags.id

使用此字符串，在我的服务器端代码中，我用逗号将其分割并循环生成标签.但是，现在我需要进行这些链接，并且我需要知道每个标签的各自ID，以便生成诸如<a href="?tag=1">New York</a>的链接.我的HTML结构如下:

With this string, in my server-side code I'm splitting it up by the comma and looping through to generate tags. However, now I need to make these links and I need to know the respective id of each of the tags in order to generate a link such as <a href="?tag=1">New York</a>. My HTML structure will be as such:

<div class=post>
  <h2>Rakim Allah!</h2>
  <div class=tags>
    <a href="/tags/1">Brooklyn</a>
    <a href="/tags/2">Manhattan</a>
    <a href="/tags/3">New York</a>
  </div>
</div>

tags表:

id    name
1     Brooklyn
2     Manhattan

tags_items表(无pk):

post  tag
1     1

推荐答案

您可以尝试将ID添加到带有不同定界符的组列表中的每个项目:

You could try adding the ID to each item in the group list w/ a different delimiter:

SELECT post.id, post.name, 
GROUP_CONCAT( CONCAT(tags.id, ';', tags.name) order by tags.name ) AS tags 
FROM post 
LEFT JOIN tags 
          ON post.id = tags.post_id;

现在，您只需对分号进行额外的拆分即可获取ID和名称.

Now you'd just have to do an additional split on semi-colons to get the id and name.

这篇关于SQL:重构GROUP_CONCAT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！