Come gestire un id nullo in modo elegante

Dati e reportistica
1

Il nostro flusso di lavoro consiste nell’assegnare ai gruppi e poi (tipicamente) i gruppi assegnano agli individui, e sto lavorando sui report che riguardano questo.

Mi ritrovo con cose del genere:

Selection_822
Selection_822633×474 13.8 KB

Idealmente, potrei usare COALESCE per trasformare quei valori NULL dell’utente in ''. Tranne che quella è una stringa, e ciò che è stato selezionato era un intero (user_id), quindi ottengo un errore. Se uso COALESCE per trasformarlo in 0, sembra ugualmente brutto, solo in un modo diverso.

Qualcuno ha padroneggiato questo?

2 Mi Piace
2

Questa è una query piuttosto complicata, penso che ci possa essere una funzionalità ninja di HTML all’interno di data explorer, quindi useresti fondamentalmente qualcosa come:

case when group_id IS NOT NULL
   then 'http://abc/groups/` || group_id::text
   else 'http://abc/user/` || user_id::text
end

E poi costruiresti un link da quello. Se vuoi solo mostrare il nome del gruppo / utente in una colonna senza che sia un hyperlink è un po’ più facile.

Qual è l’SQL che hai ora?

3

Voglio mostrare sia l’utente (quando disponibile) che il gruppo. E mi piace molto e apprezzo l’HTML ninja che mi fornisce un nome ben formattato e collegato da un ID.

Quindi costruirlo manualmente non vale la pena per me, soprattutto perché dovrei unire tabelle aggiuntive per farlo funzionare.

Ma ecco il report in questione:

-- obiettivo: mostrare assegnazioni obsolete,
--    dove obsolete significa 7 giorni dall'assegnazione

-- VEDI ANCHE: Assegnazioni obsolete per un gruppo


WITH

-- trova gli utenti che lavorano per l'azienda
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'
),

-- trova i gruppi di interesse
teams AS (
    SELECT id as group_id
    FROM groups g
    WHERE g.id not in (10, 11, 12, 13, 14 -- gruppi di livello di fiducia
                    , 1, 2, 3 -- gruppi predefiniti
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - vogliamo gli squad invece
                    , 53 -- .NET Scanner Guild
                    , 0  -- "tutti"...?
                    , 65, 66, 67 -- sottogruppi CFam
                    )
),

-- trova il team primario di ogni SonarSourcer
user_team AS (
    SELECT distinct on (user_id) -- alcuni utenti hanno 2 gruppi. Riduci (arbitrariamente) a 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
),


-- trova gli argomenti assegnati
--    che non sono né chiusi né risolti
--    che sono stati assegnati più di 7 giorni fa
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

    -- elimina gli argomenti chiusi - parte 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 -- elimina gli argomenti chiusi - parte 2
        AND pcf.id IS NULL -- elimina gli argomenti risolti
    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
),

-- trova l'ultimo post pubblico in ogni argomento assegnato
-- useremo questo per filtrare gli argomenti in cui l'ultimo poster era un membro del gruppo
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 -- regolare
    GROUP BY p.topic_id
),

-- trova l'ultimo post pubblico su ogni argomento dal gruppo assegnato
-- useremo questo per filtrare quelli a cui si sta effettivamente rispondendo
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 -- regolare
    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 -- non-SonarSourcers
            OR user_team.group_id != at.group_id)
        AND lgp.last_public_post <= current_date - INTERVAL '7' -- ultimo post 7+ giorni prima di oggi
        AND lgp.last_public_post > current_date - INTERVAL '14' -- ultimo post <14 giorni prima di oggi
    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 -- non-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)
)


-- A questo punto, ci sono ancora due righe per alcuni team (righe con user_id nullo).
-- Usa il raggruppamento e l'aggregazione per comprimere le righe ed eliminare i null
-- Inoltre, riaggiungi i team 'mancanti'
SELECT user_id
    , group_id
    , COALESCE(MAX(stale),0) AS "Obsolete (7d)"
    , COALESCE(MAX(super_stale),0) AS "Super obsolete (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super obsolete (14d)" desc, "Obsolete (7d)" desc