Je tente de construire une requête pour un classement basé sur les éléments qui comptent pour nous au cours du dernier mois :
- J’aime reçus
- Sujets résolus
- Badges obtenus
J’ai commencé par la requête Participation des utilisateurs, retiré ce dont je n’avais pas besoin, puis ajouté badges obtenus. La requête pour les badges obtenus seule ressemble à ceci :
-- [params]
-- string :interval = 1 month
-- string :trunc = month
SELECT user_id, count(id)
FROM user_badges
WHERE granted_at > date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval)
AND granted_at < date_trunc(:trunc, CURRENT_TIMESTAMP)
GROUP BY user_id
ORDER BY count DESC
Cela fonctionne parfaitement, mais lorsque j’essaie de la convertir dans la requête de Participation des utilisateurs modifiée, c’est là que tout se complique :
-- [params]
-- string :interval = 1 month
-- string :trunc = month
with
t as (
select date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) as start,
date_trunc(:trunc, CURRENT_TIMESTAMP) as end
),
pr as (
select user_id,
count(1) as visits,
sum(posts_read) as posts_read
from user_visits, t
where posts_read > 0
and visited_at > t.start
and visited_at < t.end
group by user_id
),
likes as (
select
post_actions.user_id as given_by_user_id,
posts.user_id as received_by_user_id
from t, post_actions
left join posts
on post_actions.post_id = posts.id
where post_actions.created_at > t.start
and post_actions.created_at < t.end
and post_action_type_id = 2
),
lr as (
select received_by_user_id as user_id,
count(1) as likes_received
from likes
group by user_id
),
e as (
select email, user_id
from user_emails u
where u.primary = true
),
b as (
SELECT b.user_id, count(1) as badges
FROM user_badges b, t, pr
WHERE granted_at > t.start
AND granted_at < t.end
AND pr.user_id = b.user_id
GROUP BY b.user_id
)
select pr.user_id,
username,
name,
email,
visits,
coalesce(likes_received,0) as likes_received,
coalesce(badges,0) as badges_recv
from pr
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by
likes_received desc,
visits desc
Je reçois l’erreur suivante (et je n’ai même pas encore commencé à ajouter les sujets résolus) :
PG::UndefinedColumn: ERROR: column "badges" does not exist
LINE 65: coalesce(badges,0) as badges_recv ^
Ce qui n’a pas vraiment de sens pour moi puisque badges est clairement identifié… Je suis donc un peu bloqué… Toute aide pour finaliser cette requête en ajoutant badges reçus et sujets résolus serait appréciée !