Welcome to Codidact Meta!
Codidact Meta is the meta-discussion site for the Codidact community network and the Codidact software. Whether you have bug reports or feature requests, support questions or rule discussions that touch the whole network – this is the site for you.
Remove parent tags from a post where a child tag is present
With the hierarchical tags, it would be possible to have both a tag and its parent on the same post which is means that the parent tag is unneeded. An example would be a post with both an animal and a deer tag on Outdoors.
Rather than manually removing the parent tags from these posts, it should be possible to do it with a SQL statement.
If I understand the schema right, this should select posts where this happens,
SELECT post_id
FROM posts_tags pt1
JOIN tags t1 on pt1.tag_id = t1.id
JOIN tags t2 on t1.id = t2.parent_id
JOIN post_tags pt2 on pt1.post_id = pt2.post_id and pt2.tag_id = t2.id
To remove the relationships, simply replace the select with
DELETE pt1
assuming the SQL is correct.
1 answer
It's not always correct to remove a parent tag, and therefore such removal should probably not be done automatedly — or, at least, if it is done automatedly, should be sensitive to human intervention so it doesn't re-remove a tag that someone has re-added after automated removal. For example, if mammal is a parent of deer, but a question is about how non-deer mammals in general and deer in particular get along with one another, you'd probably want both tags.
0 comment threads
1 comment thread