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.
Post History
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 an...
#3: Post edited
by
ArtOfCode
·
2020-09-18T14:39:38Z (over 4 years ago)
#2: Post edited
by
Charlie Brumbaugh
·
2020-08-17T04:05:56Z (over 4 years ago)
- 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 pt2- ```
- assuming the SQL is correct.
- 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: Initial revision
by
Charlie Brumbaugh
·
2020-08-17T03:59:05Z (over 4 years ago)
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 pt2 ``` assuming the SQL is correct.