Unser Workflow besteht darin, Gruppen zuzuweisen und dann (typischerweise) weisen die Gruppen Einzelpersonen zu, und ich arbeite an Berichten dazu.
Ich lande bei Dingen wie diesem:
Idealerweise könnte ich diese NULL-Benutzerwerte mit '' COALESCEn. Außer dass dies ein String ist und das ausgewählte ein Integer (user_id) war, also erhalte ich einen Fehler. Wenn ich es zu 0 COALESCEn, sieht es genauso schlecht aus, nur auf andere Weise.
Hat das jemand gemeistert?
Dies ist eine ziemlich knifflige Abfrage, ich denke, es könnte eine versteckte HTML-Funktion im Data Explorer geben, daher würden Sie im Grunde etwas wie Folgendes verwenden:
case when group_id IS NOT NULL
then 'http://abc/groups/` || group_id::text
else 'http://abc/user/` || user_id::text
end
Und dann daraus einen Link erstellen. Wenn Sie nur den Namen der Gruppe / des Benutzers in einer Spalte anzeigen möchten, ohne dass es ein Hyperlink ist, ist es etwas einfacher.
Wie lautet Ihre aktuelle SQL-Abfrage?
Ich möchte sowohl den Benutzer (sofern verfügbar) als auch die Gruppe anzeigen. Und ich mag und schätze das Ninja-HTML, das mir einen schön formatierten und verknüpften Namen aus einer ID gibt.
Daher lohnt es sich für mich nicht, dies manuell zu erstellen, insbesondere da ich zusätzliche Tabellen zusammenführen müsste, um es zum Laufen zu bringen.
Aber hier ist der betreffende Bericht:
-- Ziel: Veraltete Zuweisungen anzeigen,
-- wobei veraltet 7 Tage seit der Zuweisung bedeutet
-- SIEHE AUCH: Veraltete Zuweisungen für eine Gruppe
WITH
-- Finde die Benutzer, die für das Unternehmen arbeiten
SonarSourcers AS (
SELECT u.id AS user_id
FROM groups g
INNER JOIN group_users gu ON g.id=gu.group_id
INNER JOIN users u ON u.id = gu.user_id
WHERE g.name='sonarsourcers'
),
-- Finde Gruppen von Interesse
teams AS (
SELECT id as group_id
FROM groups g
WHERE g.id not in (10, 11, 12, 13, 14 -- Vertrauensstufengruppen
, 1, 2, 3 -- Eingebaute Gruppen
, 41 -- SonarSourcers
, 47 -- SonarCloud - wir wollen stattdessen die Squads
, 53 -- .NET Scanner Guild
, 0 -- "Jeder"...?
, 65, 66, 67 -- CFam Untergruppen
)
),
-- Finde das primäre Team jedes SonarSourcer
user_team AS (
SELECT distinct on (user_id) -- einige Benutzer haben 2 Gruppen. Beschränke (willkürlich) auf 1
ss.user_id, t.group_id
FROM SonarSourcers ss
JOIN group_users gu on gu.user_id=ss.user_id
JOIN teams t on t.group_id = gu.group_id
),
-- Finde zugewiesene Themen
-- die weder geschlossen noch gelöst sind
-- die vor mehr als 7 Tagen zugewiesen wurden
assigned_topics AS (
SELECT a.topic_id
, a.updated_at
, CASE WHEN assigned_to_type = 'User' THEN assigned_to_id END AS user_id
, CASE WHEN assigned_to_type = 'Group' THEN assigned_to_id ELSE user_team.group_id END AS group_id
FROM assignments a
JOIN topics t ON a.topic_id = t.id
LEFT JOIN user_team ON user_team.user_id=assigned_to_id
-- Geschlossene Themen eliminieren - Teil 1
JOIN posts p on p.topic_id = t.id
LEFT JOIN post_custom_fields pcf ON pcf.post_id=p.id AND pcf.name='is_accepted_answer'
WHERE active = true
AND a.updated_at < current_date - INTERVAL '7'
AND t.closed = false -- Geschlossene Themen eliminieren - Teil 2
AND pcf.id IS NULL -- Gelöste Themen eliminieren
GROUP BY a.topic_id, a.updated_at, assigned_to_type, assigned_to_id, user_team.group_id, t.updated_at
ORDER BY t.updated_at asc
),
-- Finde den letzten öffentlichen Beitrag in jedem zugewiesenen Thema
-- Wir werden dies verwenden, um Themen herauszufiltern, bei denen ein Gruppenmitglied der letzte Poster war
last_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
WHERE post_type = 1 -- regulär
GROUP BY p.topic_id
),
-- Finde den letzten öffentlichen Beitrag zu jedem Thema von der zugewiesenen Gruppe
-- Wir werden dies verwenden, um die Themen herauszufiltern, auf die tatsächlich reagiert wird
last_group_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
, max(created_at) as last_public_post
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
JOIN user_team on user_team.user_id=p.user_id
WHERE post_type = 1 -- regulär
GROUP BY p.topic_id
),
stale AS (
SELECT COUNT(lp.topic_id) AS stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- Nicht-SonarSourcers
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - INTERVAL '7' -- letzter Beitrag vor 7+ Tagen
AND lgp.last_public_post > current_date - INTERVAL '14' -- letzter Beitrag <14 Tagen
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
super_stale AS (
SELECT COUNT(lp.topic_id) as super_stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- Nicht-SonarSourcers
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - INTERVAL '14'
OR lgp.last_public_post IS NULL)
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
aggregated AS (
SELECT COALESCE(s.user_id,ss.user_id) AS user_id
, COALESCE(s.group_id,ss.group_id) AS group_id
, COALESCE(stale,0) AS stale
, COALESCE(super_stale,0) AS super_stale
FROM stale s
FULL JOIN super_stale ss using (user_id)
)
-- An diesem Punkt gibt es immer noch zwei Zeilen für einige Teams (Zeilen mit null user_id).
-- Verwenden Sie Gruppierung und Aggregation, um Zeilen zu reduzieren und Nullen zu eliminieren
-- Fügen Sie auch fehlende Teams wieder hinzu
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "Veraltet (7d)"
, COALESCE(MAX(super_stale),0) AS "Super veraltet (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super veraltet (14d)" desc, "Veraltet (7d)" desc