Comment gérer un id null proprement

Données et rapports
1

Notre flux de travail consiste à attribuer à des groupes et ensuite (typiquement) les groupes attribuent à des individus, et je travaille sur des rapports à ce sujet.

Je me retrouve avec des choses comme ceci :

Selection_822
Selection_822633×474 13.8 KB

Idéalement, je pourrais utiliser COALESCE pour transformer ces valeurs NULL d’utilisateur en ''. Sauf que c’est une chaîne de caractères, et ce qui a été sélectionné était un entier (user_id), donc j’obtiens une erreur. Si je le transforme en 0 avec COALESCE, cela a l’air tout aussi mauvais, juste d’une manière différente.

Quelqu’un a-t-il maîtrisé cela ?

2 « J'aime »
2

C’est une requête assez délicate, je pense qu’il pourrait y avoir une fonctionnalité HTML ninja dans l’explorateur de données, donc vous utiliseriez essentiellement quelque chose comme :

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

Et ensuite, vous construiriez un lien à partir de cela. Si vous voulez simplement afficher le nom du groupe / utilisateur dans une colonne sans qu’il s’agisse d’un hyperlien, c’est un peu plus facile.

Quel est le SQL que vous avez maintenant ?

3

Je veux afficher l’utilisateur (quand disponible) et le groupe. Et j’apprécie vraiment le HMTL ninja qui me donne un nom joliment formaté et lié à partir d’un ID.

Donc, construire cela manuellement n’en vaut pas la peine pour moi, d’autant plus que je devrais joindre des tables supplémentaires pour que cela fonctionne.

Mais voici le rapport en question :

-- objectif : afficher les affectations obsolètes,
--    où obsolète signifie 7 jours depuis l'affectation

-- VOIR AUSSI : Affectations obsolètes pour un groupe


WITH

-- trouver les utilisateurs qui travaillent pour l'entreprise
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'
),

-- trouver les groupes d'intérêt
teams AS (
    SELECT id as group_id
    FROM groups g
    WHERE g.id not in (10, 11, 12, 13, 14 -- groupes de niveaux de confiance
                    , 1, 2, 3 -- groupes intégrés
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - nous voulons les escouades à la place
                    , 53 -- .NET Scanner Guild
                    , 0  -- "tout le monde"...?
                    , 65, 66, 67 -- sous-groupes CFam
                    )
),

-- trouver l'équipe principale de chaque SonarSourcer
user_team AS (
    SELECT distinct on (user_id) -- certains utilisateurs ont 2 groupes. réduire (arbitrairement) à 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
),


-- trouver les sujets assignés
--    qui ne sont ni fermés ni résolus
--    qui ont été assignés il y a plus de 7 jours
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

    -- éliminer les sujets fermés - partie 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 -- éliminer les sujets fermés - partie 2
        AND pcf.id IS NULL -- éliminer les sujets résolus
    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
),

-- trouver le dernier message public dans chaque sujet assigné
-- nous l'utiliserons pour filtrer les sujets où un membre du groupe était le dernier auteur
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 -- régulier
    GROUP BY p.topic_id
),

-- trouver le dernier message public sur chaque sujet du groupe assigné
-- nous l'utiliserons pour filtrer ceux qui font effectivement l'objet d'une réponse
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 -- régulier
    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' -- dernier message il y a 7 jours ou plus
        AND lgp.last_public_post > current_date - INTERVAL '14' -- dernier message il y a moins de 14 jours
    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)
)


-- À ce stade, il y a encore deux lignes pour certaines équipes (lignes avec user_id nul).
-- Utiliser le regroupement et l'agrégation pour fusionner les lignes et éliminer les nuls
-- De plus, réintégrer les équipes "manquantes"
SELECT user_id
    , group_id
    , COALESCE(MAX(stale),0) AS "Obsolète (7j)"
    , COALESCE(MAX(super_stale),0) AS "Super obsolète (14j)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super obsolète (14j)" desc, "Obsolète (7j)" desc