هذا هو الاستعلام الذي ساعدني في اكتشاف المشكلة في التكوين الخاص بي. لقد مر بعض الوقت منذ أن استخدمته، لذلك لا أتذكر ما إذا كان هناك شيء فكرت في تحسينه.
يمكنك التصفية حسب العلامة أو مجموعة العلامات
-- [params]
-- null string :tag_name
-- null string :tag_group
WITH allowed_tags AS (
SELECT ct.category_id, t.id AS tag_id
FROM category_tags ct
JOIN tags t ON t.id = ct.tag_id
UNION
SELECT ctg.category_id, tgm.tag_id
FROM category_tag_groups ctg
JOIN tag_group_memberships tgm ON tgm.tag_group_id = ctg.tag_group_id
),
restricted_categories AS (
SELECT c.id, c.name
FROM categories c
WHERE c.allow_global_tags = FALSE
AND (
EXISTS (SELECT 1 FROM category_tags ct WHERE ct.category_id = c.id)
OR EXISTS (SELECT 1 FROM category_tag_groups ctg WHERE ctg.category_id = c.id)
)
),
all_tag_category_combinations AS (
SELECT t.id AS tag_id, t.name AS tag_name, rc.id AS category_id, rc.name AS category_name
FROM tags t
CROSS JOIN restricted_categories rc
WHERE t.target_tag_id IS NULL
),
not_allowed_tags AS (
SELECT atcc.tag_id, atcc.category_id, atcc.category_name
FROM all_tag_category_combinations atcc
LEFT JOIN allowed_tags al
ON al.tag_id = atcc.tag_id AND al.category_id = atcc.category_id
WHERE al.tag_id IS NULL
)
SELECT
main.id AS tag_id,
main.name AS tag_name,
COALESCE(STRING_AGG(DISTINCT syn.name, ', '), '') AS synonyms,
tg.id AS tag_group_id,
COALESCE(parent_tag.name, '') AS parent_tag_name,
CASE WHEN tg.one_per_topic THEN 'true' ELSE '' END AS one_tag_per_topic,
COALESCE(
STRING_AGG(
DISTINCT
CASE
WHEN crtg.min_count IS NOT NULL THEN crtg_cat.name || ' (' || crtg.min_count || ')'
ELSE NULL
END,
', '
),
''
) AS required_in_categories_with_min,
COALESCE(STRING_AGG(DISTINCT ctg_cat.name, ', '), '') AS tag_group_limited_to_category,
COALESCE(STRING_AGG(DISTINCT ctags_cat.name, ', '), '') AS tag_limited_to_category,
COALESCE(STRING_AGG(DISTINCT nat.category_name, ', '), '') AS not_allowed_in_categories,
CASE
WHEN COUNT(DISTINCT g.name) > 0 THEN COALESCE(STRING_AGG(DISTINCT g_use.name, ', '), '')
ELSE ''
END AS use_limited_to,
COALESCE(STRING_AGG(DISTINCT g.name, ', '), '') AS view_only_groups
FROM tags AS main
LEFT JOIN tags AS syn ON syn.target_tag_id = main.id
LEFT JOIN tag_group_memberships AS tgm ON tgm.tag_id = main.id
LEFT JOIN tag_groups AS tg ON tg.id = tgm.tag_group_id
LEFT JOIN tags AS parent_tag ON parent_tag.id = tg.parent_tag_id
LEFT JOIN tag_group_permissions tgp ON tgp.tag_group_id = tg.id AND tgp.permission_type = 3
LEFT JOIN groups g ON g.id = tgp.group_id
LEFT JOIN category_required_tag_groups AS crtg ON crtg.tag_group_id = tg.id
LEFT JOIN categories AS crtg_cat ON crtg.category_id = crtg_cat.id
LEFT JOIN category_tag_groups AS ctg ON ctg.tag_group_id = tg.id
LEFT JOIN categories AS ctg_cat ON ctg.category_id = ctg_cat.id
LEFT JOIN category_tags AS ctags ON ctags.tag_id = main.id
LEFT JOIN categories AS ctags_cat ON ctags.category_id = ctags_cat.id
LEFT JOIN not_allowed_tags AS nat ON nat.tag_id = main.id
LEFT JOIN tag_group_permissions tgp_use ON tgp_use.tag_group_id = tg.id AND tgp_use.permission_type = 1
LEFT JOIN groups g_use ON g_use.id = tgp_use.group_id
WHERE
main.target_tag_id IS NULL AND
(
:tag_name IS NULL OR
LOWER(main.name) LIKE LOWER('%' || :tag_name || '%') OR
LOWER(syn.name) LIKE LOWER('%' || :tag_name || '%')
) AND
(
:tag_group IS NULL OR
LOWER(tg.name) LIKE LOWER('%' || :tag_group || '%')
)
GROUP BY
main.id, main.name, tg.id, tg.name, parent_tag.name, tg.one_per_topic
ORDER BY main.name, tag_group_id
لغير الموظفين) فيجب أن يعرض لك بعض المعلومات الإضافية التي قد تساعد في تتبع أي قيود إضافية وضعتها عليها. على سبيل المثال 
)، قدم موين منشورًا شاملاً لاستكشاف الأخطاء وإصلاحها لشيء مشابه هنا:
