Queries Dev

• https://meta.discourse.org/t/get-last-heart-via-api/69163

---

Gestión de Temas Creados por "x" usuario

v1:

--[params]
--string :user_name = aalvarez2605
WITH usuario AS ( SELECT users.id, 
            users.username AS u_u 
            --, users.last_seen_at AS u_ls 
            FROM users )
,    stats AS ( SELECT user_stats.user_id,
            user_stats.topic_count AS total
            FROM user_stats )
,    di AS ( SELECT directory_items.user_id, 
            directory_items.topic_count AS di_tc,
            directory_items.period_type AS di_pt
            FROM directory_items )
SELECT 
stats.user_id, 
stats.total
FROM usuario, stats, di
WHERE usuario.u_u ILIKE CONCAT('%', :user_name, '%') 
AND di.user_id = stats.user_id 
AND usuario.id = di.user_id
AND di.di_pt = 1
ORDER BY stats.user_id, di.di_pt 
LIMIT 10

Esto devuelve dos columnas: USUARIO (con el enlace al usuario) y TOTAL (con el total de temas que ha creado)

v2

--[params]
--string :user_name = aalvarez2605
WITH usuario AS ( SELECT users.id, 
            users.username AS u_u 
            --, users.last_seen_at AS u_ls 
            FROM users )
,    stats AS ( SELECT user_stats.user_id,
            user_stats.topic_count AS total
            FROM user_stats )
,    di AS ( SELECT directory_items.user_id, 
            directory_items.topic_count AS di_tc,
            directory_items.period_type AS di_pt
            FROM directory_items )
--cantidad de temas cerrados y archivados
,    user_activity AS ( 
            SELECT DISTINCT count (t.id) AS t_count
            FROM topics AS t, users AS usuarios
            WHERE t.user_id = usuarios.id
                AND t.deleted_at IS NULL
                AND t.closed = TRUE )

SELECT 
stats.user_id, 
stats.total,
COUNT(user_activity) AS Cerrados
FROM usuario, stats, di, user_activity
WHERE usuario.u_u ILIKE CONCAT('%', :user_name, '%') 
AND di.user_id = stats.user_id 
AND usuario.id = di.user_id
AND di.di_pt = 1
GROUP BY stats.user_id, stats.total, di.di_pt
ORDER BY stats.user_id, di.di_pt 
LIMIT 10

Quiero agregar más columnas:

• Además del TOTAL; TOTAL ABIERTOS; TOTAL SOLUCIONADOS; TOTAL CERRADOS

temas AS ( SELECT t.id
                --, c.id,
                , t.user_id AS user,
                t.closed AS cerrados,
                t.archived AS archivados,
                t.category_id AS categoria
                FROM topics t, categories c
                WHERE t.id = c.topic_id)