How to handle a null id nicely

Our workflow is to assign to groups & then (typically) the groups assign to individuals, and I’m working on reporting around that.

I’m ending up with things like this:

Selection_822

Ideally I’d be able to COALESCE those NULL user values to ''. Except that’s a string, and what was selected was an integer (user_id), so I get an error. If I COALESCE it to 0, it looks equally bad, just in a different way.

Has anyone mastered this?

2 Likes

This is a pretty tricky query, I think there may be some ninja HTML feature inside data explorer, so you would basically use something like:

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

And then construct a link from that. If you want to just show the name of the group / user in one column without it being a hyperlink it is a bit easier.

What is the SQL you have now?

I want to show both user (when available) and group. And I really like and appreciate the ninja HMTL that gives me a nicely formatted and linked name from an id.

So building that manually just isn’t worth it for me, particularly since I’d have to join extra tables back in to make it work.

But here’s the report in question:

-- goal: show stale assignments,
--    where stale is 7 days since assignment

-- SEE ALSO: Stale assignments for a group


WITH

-- find the users who work for the company
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'
),

-- find groups of interest
teams AS (
    SELECT id as group_id
    FROM groups g
    WHERE g.id not in (10, 11, 12, 13, 14 -- trust level groups
                    , 1, 2, 3 -- built-in groups
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - we want the squads instead
                    , 53 -- .NET Scanner Guild
                    , 0  -- "everyone"...?
                    , 65, 66, 67 -- CFam sub-groups
                    ) 
),

-- find each SonarSourcer's primary team
user_team AS (
    SELECT distinct on (user_id) -- some users have 2 groups. narrow (arbitrarily) to 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
),


-- find assigned topics 
--    that are neither closed nor solved
--    that were assigned more than 7 days ago
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

    -- eliminate closed topics - part 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 - INTEGER '7'
        AND t.closed = false -- eliminate closed topics - part 2
        AND pcf.id IS NULL -- eliminate solved topics
    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
),

-- find the last public post in each assigned topic
-- we'll use this to filter out topics were a group member was last poster
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 -- regular
    GROUP BY p.topic_id
), 

-- find the last public post on each topic from the assigned group
-- we'll use this to filter out the ones actually being responded to
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 -- regular
    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 - 7 -- last post 7+ days before today
        AND lgp.last_public_post > current_date - 14 -- last post <14 days before today
    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 - 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)
)


-- At this point, there are still two rows for some teams (rows w/null user_id).
-- Use grouping & aggregation to collapse rows & eliminate nulls
-- Also, add back in 'missing' teams
SELECT user_id
    , group_id
    , COALESCE(MAX(stale),0) AS "Stale (7d)"
    , COALESCE(MAX(super_stale),0) AS "Super stale (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super stale (14d)" desc, "Stale (7d)" desc