I have a tag field for a blog posts. tags have unique id but their displayName might be duplicated. What I want is a query that selects posts and in all_tags
field we get couples of (id,displayName) is this way:
我有一个博客帖子的标签字段。标签具有唯一ID,但其displayName可能重复。我想要的是一个选择帖子的查询,在all_tags字段中,我们得到(id,displayName)夫妇是这样的:
id1,name1;id2,name2;id3,name3
My query looks like:
我的查询看起来像:
select ....
CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags
Join ...post content ...
Join ...post_tags ...
Join ...tags ...
ORDER BY posts.id
This line causes problem:
此行导致问题:
CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags
How should I modify it?
我应该如何修改它?
Some people use an inner (SELECT .. FROM)
but as I have heard, it is so inefficien
有些人使用内部(SELECT .. FROM),但正如我所听到的,它是如此无效
SELECT `posts`.*,`categories`.*,`creators`.*,`editors`.*
CONCAT_WS(';', DISTINCT GROUP_CONCAT(CONCAT_WS(',',tags.id,tags.displayName))) AS all_ids
FROM (`posts`)
LEFT JOIN `languages` ON `posts`.`language_id`=`languages`.`id`
LEFT JOIN `users` as creators ON `posts`.`creatorUser_id`=`creators`.`id`
LEFT JOIN `users` as editors ON `posts`.`lastEditorUser_id`=`editors`.`id`
LEFT JOIN `userProfiles` as editors_profile ON `editors`.`profile_id`=`editors_profile`.`id`
LEFT JOIN `categories` ON `posts`.`category_id`=`categories`.`id`
LEFT JOIN `postTags` ON `postTags`.`post_id`=`posts`.`id`
LEFT JOIN `tags` ON `postTags`.`tag_id`=`tags`.`id`
LEFT JOIN `postTags` as `nodetag_checks` ON `nodetag_checks`.`post_id`=`posts`.`id`
LEFT JOIN `tags` as `tag_checks` ON `nodetag_checks`.`tag_id`=`tag_checks`.`id`
WHERE ( 9 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 10 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 11 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`))
GROUP BY `posts`.`id` ORDER BY `posts`.`created` desc LIMIT 0, 20
1 个解决方案
#1
52
Try this:
尝试这个:
GROUP_CONCAT(
DISTINCT CONCAT(tags.id,',',tags.displayName)
ORDER BY posts.id
SEPARATOR ';'
)
#1
52
Try this:
尝试这个:
GROUP_CONCAT(
DISTINCT CONCAT(tags.id,',',tags.displayName)
ORDER BY posts.id
SEPARATOR ';'
)