CRPD-pipelinen

Custom SQL query returning 29 rows (hide)

WITH lvl AS (
  SELECT 'fn' AS slug, 1 AS sort_order
  UNION ALL SELECT 'europaradet', 2
  UNION ALL SELECT 'eu', 3
  UNION ALL SELECT 'nordisk', 4
  UNION ALL SELECT 'nationell', 5
  UNION ALL SELECT 'kommunal', 6
  UNION ALL SELECT 'global', 7
)
SELECT
  'avsändare' AS kategori,
  s.name_short,
  s.name_full,
  lv.label_sv AS level,
  COALESCE(lvl.sort_order, 99) AS level_order,
  s.fk_relevance,
  s.website AS lank
FROM senders s
LEFT JOIN vocab_levels lv ON s.level = lv.slug
LEFT JOIN lvl ON s.level = lvl.slug
WHERE EXISTS (
        SELECT 1 FROM json_each(s.discrimination_grounds)
        WHERE value = 'funktionsnedsattning'
      )
   OR EXISTS (
        SELECT 1 FROM json_each(s.instruments)
        WHERE value = 'crpd'
      )

UNION ALL

SELECT
  'publikation' AS kategori,
  s.name_short,
  p.title AS name_full,
  lv.label_sv AS level,
  COALESCE(lvl.sort_order, 99) AS level_order,
  s.fk_relevance,
  p.url AS lank
FROM publications p
JOIN senders s ON p.sender_id = s.id
LEFT JOIN vocab_levels lv ON s.level = lv.slug
LEFT JOIN lvl ON s.level = lvl.slug
WHERE EXISTS (
        SELECT 1 FROM json_each(COALESCE(p.discrimination_grounds, s.discrimination_grounds))
        WHERE value = 'funktionsnedsattning'
      )
   OR EXISTS (
        SELECT 1 FROM json_each(COALESCE(p.instruments, s.instruments))
        WHERE value = 'crpd'
      )

ORDER BY level_order, name_short;

Edit SQL