[SQL] Suivi des commentaires

Commentaires publics :

SELECT * FROM (SELECT
	sc.*,
    array_remove(array_agg(scut.fk_user), NULL)::text[] AS user_targets,
    array_remove(array_agg(ot.uid), NULL)::text[] AS org_targets
FROM shantytown_comments sc
LEFT JOIN shantytown_covid_comments scv ON scv.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_user_targets scut ON scut.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_organization_targets scot ON scot.fk_comment = sc.shantytown_comment_id
LEFT JOIN organizations o ON scot.fk_organization = o.organization_id
LEFT JOIN organization_types ot ON o.fk_type = ot.organization_type_id
WHERE scv.shantytown_covid_comment_id IS NULL
GROUP BY sc.shantytown_comment_id) t
WHERE
(
  cardinality(user_targets) = 0
  AND
  cardinality(org_targets) = 0
);

Commentaires réservés aux préfs et DDETS :

SELECT * FROM (SELECT
	sc.*,
    array_remove(array_agg(scut.fk_user), NULL)::text[] AS user_targets,
    array_remove(array_agg(ot.uid), NULL)::text[] AS org_targets
FROM shantytown_comments sc
LEFT JOIN shantytown_covid_comments scv ON scv.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_user_targets scut ON scut.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_organization_targets scot ON scot.fk_comment = sc.shantytown_comment_id
LEFT JOIN organizations o ON scot.fk_organization = o.organization_id
LEFT JOIN organization_types ot ON o.fk_type = ot.organization_type_id
WHERE scv.shantytown_covid_comment_id IS NULL
GROUP BY sc.shantytown_comment_id) t
WHERE
(
  cardinality(user_targets) = 0
  AND 
  (org_targets @> array['pref_region', 'pref_departement', 'ddets'] AND org_targets <@ array['pref_region', 'pref_departement', 'ddets'])
);

Commentaires privés (hors ceux réservés aux prefs et DDETS) :

SELECT * FROM (SELECT
	sc.*,
    array_remove(array_agg(scut.fk_user), NULL)::text[] AS user_targets,
    array_remove(array_agg(ot.uid), NULL)::text[] AS org_targets
FROM shantytown_comments sc
LEFT JOIN shantytown_covid_comments scv ON scv.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_user_targets scut ON scut.fk_comment = sc.shantytown_comment_id
LEFT JOIN shantytown_comment_organization_targets scot ON scot.fk_comment = sc.shantytown_comment_id
LEFT JOIN organizations o ON scot.fk_organization = o.organization_id
LEFT JOIN organization_types ot ON o.fk_type = ot.organization_type_id
WHERE scv.shantytown_covid_comment_id IS NULL
GROUP BY sc.shantytown_comment_id) t
WHERE
(
  cardinality(user_targets) > 0
  OR
  cardinality(org_targets) > 0
)
AND
(
  cardinality(user_targets) > 0
  OR
  NOT(org_targets @> array['pref_region', 'pref_departement', 'ddets'] AND org_targets <@ array['pref_region', 'pref_departement', 'ddets'])
);