开发者

SQL: Refactoring a GROUP_CONCAT query

开发者 https://www.devze.com 2022-12-12 22:52 出处:网络
My eventual goal is to generate tag links for blog posts, the tags relating to the post. Currently my query has a GROUP_CONCAT which grabs relating tags and the returning tags column value is somet

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

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

'Brooklyn, Manhattan, New York'

from

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 t开发者_运维问答ags
  ON tags_items.tag = tags.id

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 table:

id    name
1     Brooklyn
2     Manhattan

tags_items table ( no pk ):

post  tag
1     1


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;

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

0

精彩评论

暂无评论...
验证码 换一张
取 消