J’ai essayé de recréer le rapport « exigences pour le niveau de confiance 3 » que le personnel peut voir sur la page de profil administrateur d’un utilisateur, en tant que requête Data Explorer.
La motivation derrière cela est que, grâce à la nouvelle fonctionnalité permettant de limiter une requête à l’ID de l’utilisateur actuel, les utilisateurs TL2 peuvent vérifier leur propre progression sans avoir à demander au personnel.
La requête utilise les valeurs configurées dans les paramètres du site et les compare aux statistiques de l’utilisateur. J’ai remarqué de minuscules différences entre la vue administrateur et les résultats de la requête, probablement dues à l’arrondi. La logique de promotion au niveau TL3 est assez complexe, donc le SQL est nécessairement un peu compliqué, et il peut encore y avoir des erreurs. Je serais vraiment reconnaissant de tout retour afin que je puisse corriger les éventuelles erreurs.
Voici la requête actuelle :
Actuellement, elle n’est pas limitée à current_user_id car, pour les tests, il est plus facile de pouvoir sélectionner un utilisateur sur lequel exécuter la requête. Pour modifier cela, vous changeriez la deuxième ligne en -- current_user_id :user_id
-- [params]
-- user_id :user_id
WITH settings AS (
SELECT
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_time_period'), 100) AS time_period,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_days_visited'), 50) AS min_days_visited,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) AS min_topics_replied_to,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) AS topics_viewed_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) AS topics_viewed_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) AS posts_read_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) AS posts_read_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_all_time'), 200) AS min_topics_viewed_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) AS min_posts_read_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_given'), 30) AS min_likes_given,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_received'), 20) AS min_likes_received,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) AS max_flagged
),
period AS (
SELECT
NOW() - (time_period || ' days')::interval AS since,
time_period
FROM settings
),
global_counts AS (
SELECT
(SELECT COUNT(*) FROM topics t, period p
WHERE t.created_at > p.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_recent,
(SELECT COUNT(*)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS posts_recent
),
user_stats AS (
SELECT
u.id AS user_id,
(SELECT COUNT(*) FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
AND uv.posts_read > 0
) AS days_visited,
(SELECT COUNT(DISTINCT p.topic_id)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.user_id = u.id
AND p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.user_id <> u.id
) AS topics_replied_to,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id, period pr
WHERE tv.user_id = u.id
AND tv.viewed_at > pr.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_recent,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
WHERE tv.user_id = u.id
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_all_time,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
) AS posts_read_recent,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv
WHERE uv.user_id = u.id
) AS posts_read_all_time,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 1
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_given,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received,
(SELECT COUNT(DISTINCT DATE(ua.created_at))
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_days,
(SELECT COUNT(DISTINCT ua.acting_user_id)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_users
FROM users u
WHERE u.id = :user_id
),
flags AS (
SELECT
COUNT(DISTINCT p.id) AS flagged_posts,
COUNT(DISTINCT pa.user_id) AS flagged_users
FROM posts p
JOIN post_actions pa ON pa.post_id = p.id
JOIN period pr ON true
WHERE p.user_id = :user_id
AND p.created_at > pr.since
AND pa.agreed_at IS NOT NULL
AND pa.user_id <> :user_id
AND (p.spam_count > 0 OR p.inappropriate_count > 0)
),
penalty_history AS (
SELECT
uh.target_user_id AS user_id,
SUM(
CASE
WHEN uh.action = 30 THEN 1
WHEN uh.action = 31 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS silenced_delta,
SUM(
CASE
WHEN uh.action = 10 THEN 1
WHEN uh.action = 11 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS suspended_delta
FROM user_histories uh
WHERE uh.created_at > NOW() - INTERVAL '6 months'
AND uh.target_user_id = :user_id
GROUP BY uh.target_user_id
),
penalties AS (
SELECT
u.id AS user_id,
COALESCE(ph.silenced_delta, 0)
+ CASE
WHEN u.silenced_till IS NOT NULL AND u.silenced_till > NOW()
THEN 1 ELSE 0
END AS silenced,
COALESCE(ph.suspended_delta, 0)
+ CASE
WHEN u.suspended_till IS NOT NULL AND u.suspended_till > NOW()
THEN 1 ELSE 0
END AS suspended
FROM users u
LEFT JOIN penalty_history ph ON ph.user_id = u.id
WHERE u.id = :user_id
)
SELECT
requirement,
required_value,
user_value,
fulfill
FROM (
SELECT
'Topics Replied To (last ' || s.time_period || ' days)' AS requirement,
s.min_topics_replied_to AS required_value,
us.topics_replied_to AS user_value,
CASE
WHEN us.topics_replied_to >= s.min_topics_replied_to
THEN '✔' ELSE '✘'
END AS fulfill
FROM user_stats us, settings s
UNION ALL
SELECT
'Topics Viewed (last ' || s.time_period || ' days)',
LEAST(CEIL(gc.topics_recent * s.topics_viewed_percent / 100.0), s.topics_viewed_cap)::int,
us.topics_viewed_recent,
CASE WHEN us.topics_viewed_recent >=
LEAST(gc.topics_recent * s.topics_viewed_percent / 100, s.topics_viewed_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Topics Viewed (all time)',
s.min_topics_viewed_all_time,
us.topics_viewed_all_time,
CASE WHEN us.topics_viewed_all_time >= s.min_topics_viewed_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (last ' || s.time_period || ' days)',
LEAST( CEIL(gc.posts_recent * s.posts_read_percent / 100.0), s.posts_read_cap)::int,
us.posts_read_recent,
CASE WHEN us.posts_read_recent >=
LEAST(gc.posts_recent * s.posts_read_percent / 100, s.posts_read_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Posts Read: unique days (last ' || s.time_period || ' days)',
s.min_days_visited,
us.days_visited,
CASE WHEN us.days_visited >= s.min_days_visited THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (all time)',
s.min_posts_read_all_time,
us.posts_read_all_time,
CASE WHEN us.posts_read_all_time >= s.min_posts_read_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Flagged Posts (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_posts,
CASE WHEN f.flagged_posts <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Users Who Flagged (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_users,
CASE WHEN f.flagged_users <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Likes Given (last ' || s.time_period || ' days)',
s.min_likes_given,
us.likes_given,
CASE WHEN us.likes_given >= s.min_likes_given THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received (last ' || s.time_period || ' days)',
s.min_likes_received,
us.likes_received,
CASE WHEN us.likes_received >= s.min_likes_received THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique days (last ' || s.time_period || ' days)',
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int,
us.likes_received_days,
CASE WHEN us.likes_received_days >=
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique users (last ' || s.time_period || ' days)',
CEIL(s.min_likes_received / 4.0)::int,
us.likes_received_users,
CASE WHEN us.likes_received_users >= CEIL(s.min_likes_received / 4.0)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Silenced (last 6 months)',
0,
p.silenced,
CASE WHEN p.silenced = 0 THEN '✔' ELSE '✘' END
FROM penalties p
UNION ALL
SELECT
'Suspended (last 6 months)',
0,
p.suspended,
CASE WHEN p.suspended = 0 THEN '✔' ELSE '✘' END
FROM penalties p
) x
Exemple de résultat :
