J’ai rencontré un problème similaire aujourd’hui sur un site où je tentais de déterminer quels paramètres du site tl3 requires empêchaient la promotion des utilisateurs. Voici une requête qui peut être utilisée à cet effet. Elle utilise des requêtes similaires à celles employées par Discourse pour vérifier si un utilisateur doit être promu. Les exigences du niveau TL3 sont toutes disponibles en tant que paramètres de requête. Elles sont définies sur leurs valeurs par défaut.
Si le paramètre show all results (afficher tous les résultats) est coché, tous les résultats seront renvoyés. S’il est décoché, seuls les résultats des utilisateurs qui obtiendraient le statut TL3 avec les valeurs définies dans les autres paramètres de la requête seront renvoyés.
La requête est longue. Si quelqu’un la teste et remarque des erreurs, veuillez me le faire savoir. Les noms des colonnes sont longs pour faciliter le débogage. Ils devraient probablement être raccourcis.
--[params]
-- int :tl_time_period = 100
-- int :tl_requires_days_visited = 50
-- int :tl_requires_topics_replied_to = 10
-- int :tl_requires_topics_viewed = 25
-- int :tl_requires_topics_viewed_cap = 500
-- int :tl_requires_posts_read = 25
-- int :tl_requires_posts_read_cap = 20000
-- int :tl_requires_max_flagged = 5
-- int :tl_requires_topics_viewed_all_time = 200
-- int :tl_requires_posts_read_all_time = 500
-- int :tl_requires_likes_given = 30
-- int :tl_requires_likes_received = 20
-- boolean :show_all_results = true
WITH tl3_candidates AS (
SELECT id AS user_id FROM users
WHERE trust_level = 2
AND last_seen_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
),
min_topics_viewed AS (
SELECT
LEAST(COUNT(*) * (:tl_requires_topics_viewed / 100.0), :tl_requires_topics_viewed_cap) AS min_topics_viewed
FROM topics
WHERE visible = true
AND archetype = 'regular'
AND created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
),
min_posts_read AS (
SELECT
LEAST(COUNT(*) * (:tl_requires_posts_read / 100.0), :tl_requires_posts_read_cap) AS min_posts_read
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
),
min_likes_received_days AS (
SELECT
LEAST(:tl_requires_likes_received::float / 3.0, 0.75 * :tl_time_period::float)
),
days_visited AS (
SELECT
uv.user_id,
COUNT(uv.user_id) AS jours_visites
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at > CURRENT_DATE - (:tl_time_period || ' jours')::interval
AND posts_read >= 0
GROUP BY uv.user_id
),
num_topics_replied_to AS (
SELECT
p.user_id,
COUNT(DISTINCT p.topic_id) AS nombre_reponses_topics
FROM posts p
JOIN topics t ON t.id = p.topic_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.user_id <> t.user_id
AND t.archetype <> 'private_message'
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND p.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
GROUP BY p.user_id
),
topics_viewed AS (
SELECT
tv.user_id,
COUNT(tv.user_id) AS nombre_vues_topics
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
JOIN tl3_candidates c ON c.user_id = tv.user_id
WHERE t.archetype <> 'private_message'
AND viewed_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
GROUP BY tv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(posts_read) AS posts_lus
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
GROUP BY uv.user_id
),
num_flagged_posts AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.post_id) AS nombre_posts_signales
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
AND (spam_count > 0 OR inappropriate_count > 0)
AND agreed_at IS NOT NULL
AND pa.user_id <> p.user_id
GROUP BY p.user_id
),
num_flagged_by_users AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.user_id) AS nombre_utilisateurs_signaleurs
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
AND (spam_count > 0 OR inappropriate_count > 0)
AND agreed_at IS NOT NULL
AND pa.user_id <> p.user_id
GROUP BY p.user_id
),
topics_viewed_all_time AS (
SELECT
tv.user_id,
COUNT(topic_id) AS vues_topics_total
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
JOIN tl3_candidates c ON c.user_id = tv.user_id
WHERE t.archetype = 'regular'
GROUP BY tv.user_id
),
posts_read_all_time AS (
SELECT
uv.user_id,
SUM(posts_read) AS posts_lus_total
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
GROUP BY uv.user_id
),
num_likes_given AS (
SELECT
ua.user_id,
COUNT(*) AS nombre_likes_donnes
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id
JOIN tl3_candidates c ON c.user_id = ua.user_id
WHERE ua.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
AND t.archetype = 'regular'
AND ua.action_type = 1
GROUP BY ua.user_id
),
num_likes_received AS (
SELECT
ua.user_id,
COUNT(*) AS nombre_likes_recus,
COUNT(DISTINCT acting_user_id) AS nombre_utilisateurs_likes_recus,
COUNT(DISTINCT ua.created_at::date) AS nombre_jours_likes_recus
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id
JOIN tl3_candidates c ON c.user_id = ua.user_id
WHERE ua.created_at >= CURRENT_DATE - (:tl_time_period || ' jours')::interval
AND t.archetype = 'regular'
AND ua.action_type = 2
GROUP BY ua.user_id
),
candidate_results AS(
SELECT
c.user_id,
COALESCE(jours_visites, 0) AS jours_visites,
COALESCE(jours_visites, 0) >= :tl_requires_days_visited AS critere_visites_respecte,
COALESCE(nombre_reponses_topics, 0) AS nombre_reponses_topics,
COALESCE(nombre_reponses_topics, 0) >= :tl_requires_topics_replied_to AS critere_reponses_respecte,
COALESCE(nombre_vues_topics, 0) AS nombre_vues_topics,
COALESCE(nombre_vues_topics, 0) >= (SELECT * FROM min_topics_viewed) AS critere_vues_topics_respecte,
COALESCE(posts_lus, 0) AS posts_lus,
COALESCE(posts_lus, 0) >= (SELECT * FROM min_posts_read) AS critere_posts_lus_respecte,
COALESCE(nombre_posts_signales, 0) AS nombre_posts_signales,
COALESCE(nombre_posts_signales, 0) <= :tl_requires_max_flagged AS critere_posts_signales_respecte,
COALESCE(nombre_utilisateurs_signaleurs, 0) AS nombre_utilisateurs_signaleurs,
COALESCE(nombre_utilisateurs_signaleurs, 0) <= :tl_requires_max_flagged AS critere_signaleurs_respecte,
COALESCE(vues_topics_total, 0) AS vues_topics_total,
COALESCE(vues_topics_total, 0) >= :tl_requires_topics_viewed_all_time AS critere_vues_topics_total_respecte,
COALESCE(posts_lus_total, 0) AS posts_lus_total,
COALESCE(posts_lus_total, 0) >= :tl_requires_posts_read_all_time AS critere_posts_lus_total_respecte,
COALESCE(nombre_likes_donnes, 0) AS nombre_likes_donnes,
COALESCE(nombre_likes_donnes, 0) >= :tl_requires_likes_given AS critere_likes_donnes_respecte,
COALESCE(nombre_likes_recus, 0) AS nombre_likes_recus,
COALESCE(nombre_likes_recus, 0) >= :tl_requires_likes_received AS critere_likes_recus_respecte,
COALESCE(nombre_utilisateurs_likes_recus, 0) AS nombre_utilisateurs_likes_recus,
COALESCE(nombre_utilisateurs_likes_recus, 0) >= :tl_requires_likes_received::float / 4.0 AS critere_utilisateurs_likes_recus_respecte,
COALESCE(nombre_jours_likes_recus, 0) AS nombre_jours_likes_recus,
COALESCE(nombre_jours_likes_recus, 0) >= (SELECT * FROM min_likes_received_days) AS critere_jours_likes_recus_respecte
FROM tl3_candidates c
LEFT JOIN days_visited dv ON dv.user_id = c.user_id
LEFT JOIN num_topics_replied_to ntr ON ntr.user_id = c.user_id
LEFT JOIN topics_viewed tv ON tv.user_id = c.user_id
LEFT JOIN posts_read pr ON pr.user_id = c.user_id
LEFT JOIN num_flagged_posts nfp ON nfp.user_id = c.user_id
LEFT JOIN num_flagged_by_users nfu ON nfu.user_id = c.user_id
LEFT JOIN topics_viewed_all_time tvat ON tvat.user_id = c.user_id
LEFT JOIN posts_read_all_time prat ON prat.user_id = c.user_id
LEFT JOIN num_likes_given nlg ON nlg.user_id = c.user_id
LEFT JOIN num_LIkes_received nlr ON nlr.user_id = c.user_id
)
SELECT * FROM candidate_results
WHERE CASE WHEN :show_all_results THEN true ELSE critere_visites_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_reponses_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_vues_topics_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_posts_lus_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_posts_signales_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_signaleurs_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_vues_topics_total_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_posts_lus_total_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_likes_donnes_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_likes_recus_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_utilisateurs_likes_recus_respecte END
AND CASE WHEN :show_all_results THEN true ELSE critere_jours_likes_recus_respecte END
ORDER BY jours_visites DESC