I've made a thread about this before, but this time I'll elaborate the intentions of it in detail:
first of all, I'll provide the SQL I'm using to generate the favgroup, on the off chance there's an oversight that i made, or otherwise some improvement:
Show
WITH pool_post_ids AS ( SELECT post_id FROM `danbooru1.danbooru_public.pools`, UNNEST(post_ids) post_id ), distinct_tag_strings AS ( SELECT DISTINCT tag_string FROM `danbooru1.danbooru_public.posts` WHERE id NOT IN (SELECT post_id FROM pool_post_ids) GROUP BY tag_string HAVING COUNT(DISTINCT id) > 1 ) SELECT id FROM `danbooru1.danbooru_public.posts` WHERE tag_string IN ( SELECT tag_string FROM distinct_tag_strings ) AND has_children = false AND parent_id is null and is_deleted = false and is_banned = false AND id NOT IN (SELECT post_id FROM pool_post_ids) ORDER BY tag_string LIMIT 10000
Second is the goal of what this is meant to do: I'm of the theory that every post on this site should have a unique tag...permutation? that one could search and find the exact post they are looking for, at least for the most part.
So far what I've found with this search was that posts that currently do have the same tags are either:
- unparented duplicates
- tags copy pasted from one post to another, often for a particular character, one extremes example is Soledad and their sockpuppets causing 22 bayonetta posts to have the same 24 gen tags
Due to the way I've generated these, sometimes with one of the results won't have it's others in the favgroup, due to being deleted. I've done this mostly to focus on active posts, but sometimes it can be useful to look for the deleted post, so I've created the following bookmark to do so:
Show
javascript:(function() { var tags = Array.from(document.querySelectorAll(".tag-type-0")).map(x => encodeURIComponent(x.dataset.tagName)).join('+'); var url = window.location.origin + "/posts?tags=" + tags + "+status:any"; window.location.href = url;})();
Here is a favgroup i generated using the above: favgroup #25806