postgre如何在SQL中检查所有标记是否应用于这些项

Question

我有四个表：itemsrelationshipstagsitem_to_tags。关系可以连接两个项目，items_to_tags将标签连接到这样的项目：

items

id
0
1
2
3
4

当没有一个标签像任何一个和所有标签一样适用时，我不得不做一个解决方案。如果有人觉得有用的话，这就是我目前的解决方案。如果有人有任何改进的方法，请告诉我：

CREATE OR REPLACE FUNCTION get_items_descendants(item_id int, tag_filters integer[] = array[]::integer[], operation text = 'any')
RETURNS SETOF items AS $$
WITH RECURSIVE descendants AS (
SELECT i.id, r.target_item_id, -1 as tag_id
FROM items i
LEFT OUTER JOIN relationships r ON (i.id = r.source_item_id)
WHERE i.id = item_id
UNION
SELECT i.id, r.target_item_id, t.tag_id
FROM descendants d
JOIN items i ON (d.target_item_id = i.id) 
LEFT OUTER JOIN relationships r ON (i.id = r.source_item_id) 
LEFT OUTER JOIN items_to_tags t ON (i.id = t.item_id)
WHERE (operation = 'none' AND NOT EXISTS (SELECT * FROM items_to_tags WHERE item_id = i.id AND tag_id = ANY(tag_filters)))
OR (operation != 'none' AND (cardinality(tag_filters::integer[]) = 0 OR t.tag_id = ANY(tag_filters)))
)
SELECT * FROM items i 
WHERE (operation = 'all'
AND i.id IN (
SELECT id FROM (
SELECT id, array_agg(tag_id) as tag_ids
FROM descendants d
WHERE id != item_id
GROUP BY id
) as d
WHERE d.tag_ids @> tag_filters
)
)
OR (operation != 'all'
AND i.id IN (
SELECT id FROM descendants WHERE id != item_id
)
);
$$ LANGUAGE sql STABLE;