Danbooru

Gardening posts with the exact same tags

Posted under Tags

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:

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

1