Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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

+1
−2

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.

History
Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

1 comment thread

General comments (4 comments)

1 answer

+4
−1

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.

History
Why does this post require moderator attention?
You might want to add some details to your flag.

0 comment threads

Sign up to answer this question »