Как корректно обработать null id

Создание сообщества Данные и отчетность
1

Наш рабочий процесс заключается в том, что сначала назначаем группы, а затем (как правило) группы назначают задачи конкретным исполнителям, и я работаю над отчетностью по этому процессу.

В итоге у меня получается что-то вроде этого:

Selection_822
Selection_822633×474 13.8 KB

В идеале я бы хотел использовать COALESCE для замены этих NULL значений пользователей на ''. Однако это строка, а было выбрано целое число (user_id), поэтому возникает ошибка. Если я использую COALESCE с значением 0, результат выглядит так же плохо, просто по-другому.

Кто-нибудь уже решил эту проблему?

2

Это довольно хитрый запрос. Похоже, внутри Data Explorer есть какая-то скрытая функция HTML, поэтому вы можете использовать что-то вроде:

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

А затем создать из этого ссылку. Если же вы просто хотите отобразить имя группы или пользователя в одном столбце без гиперссылки, это немного проще.

Какой у вас сейчас SQL-запрос?

3

Я хочу отображать и пользователя (если он доступен), и группу. И мне очень нравится HTML-магия ninja, которая позволяет получить красиво отформатированное и связанное имя по ID.

Поэтому вручную собирать это просто не стоит, особенно учитывая, что мне пришлось бы присоединять дополнительные таблицы, чтобы это работало.

Но вот сам отчёт:

-- цель: показать устаревшие назначения,
--    где устаревшее — это 7 дней с момента назначения

-- СМОТРИ ТАКЖЕ: Устаревшие назначения для группы


WITH

-- найти пользователей, работающих в компании
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'
),

-- найти группы интереса
teams AS (
    SELECT id as group_id
    FROM groups g
    WHERE g.id not in (10, 11, 12, 13, 14 -- группы уровней доверия
                    , 1, 2, 3 -- встроенные группы
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud — мы хотим команды вместо этого
                    , 53 -- Гильдия .NET Scanner
                    , 0  -- "все"...?
                    , 65, 66, 67 -- подгруппы CFam
                    ) 
),

-- найти основную команду каждого SonarSourcer
user_team AS (
    SELECT distinct on (user_id) -- у некоторых пользователей 2 группы. ограничим (произвольно) до 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
),


-- найти назначенные темы 
--    которые не закрыты и не решены
--    которые были назначены более 7 дней назад
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

    -- исключить закрытые темы — часть 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 -- исключить закрытые темы — часть 2
        AND pcf.id IS NULL -- исключить решённые темы
    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
),

-- найти последний публичный пост в каждой назначенной теме
-- мы будем использовать это для фильтрации тем, где последним постером был член группы
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 -- обычный
    GROUP BY p.topic_id
), 

-- найти последний публичный пост в каждой теме от назначенной группы
-- мы будем использовать это для фильтрации тех, на которые фактически отвечают
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 -- обычный
    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 -- не SonarSourcers
            OR user_team.group_id != at.group_id)
        AND lgp.last_public_post <= current_date - 7 -- последний пост 7+ дней назад
        AND lgp.last_public_post > current_date - 14 -- последний пост <14 дней назад
    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 -- не 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)
)


-- На этом этапе для некоторых команд всё ещё есть две строки (строки с null user_id).
-- Используйте группировку и агрегацию для сжатия строк и устранения null
-- Также добавьте обратно 'отсутствующие' команды
SELECT user_id
    , group_id
    , COALESCE(MAX(stale),0) AS "Устаревшие (7д)"
    , COALESCE(MAX(super_stale),0) AS "Сильно устаревшие (14д)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Сильно устаревшие (14д)" desc, "Устаревшие (7д)" desc