今日は、どの tl3 requires サイト設定がユーザーの昇進を妨げているかを特定しようとして、似たような状況に遭遇しました。それを実現するためのクエリを以下に示します。これは、Discourse がユーザーの昇進判定を行う際に使用するクエリと類似しています。TL3 の要件はすべてクエリパラメータとして利用可能で、デフォルト値に設定されています。
show all results パラメータがチェックされている場合、すべての結果が返されます。チェックされていない場合、クエリの他のパラメータで設定された値に基づいて TL3 ステータスが与えられるユーザーの結果のみが返されます。
このクエリは長くなっています。もし誰かが試してエラーに気づいた場合は、お知らせください。列名はデバッグを容易にするために長く設定されていますが、おそらく短縮すべきでしょう。
--[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 || ' days')::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 || ' days')::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 || ' days')::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 days_visited
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at > CURRENT_DATE - (:tl_time_period || ' days')::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 topic_reply_count
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 || ' days')::interval
GROUP BY p.user_id
),
topics_viewed AS (
SELECT
tv.user_id,
COUNT(tv.user_id) AS topic_view_count
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 || ' days')::interval
GROUP BY tv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(posts_read) AS posts_read
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
GROUP BY uv.user_id
),
num_flagged_posts AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.post_id) AS num_flagged_posts
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 || ' days')::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 num_flagged_by_users
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 || ' days')::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 topics_viewed_all_time
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_read_all_time
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 num_likes_given
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 || ' days')::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 num_likes_received,
COUNT(DISTINCT acting_user_id) AS num_likes_received_users,
COUNT(DISTINCT ua.created_at::date) AS num_likes_received_days
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 || ' days')::interval
AND t.archetype = 'regular'
AND ua.action_type = 2
GROUP BY ua.user_id
),
candidate_results AS(
SELECT
c.user_id,
COALESCE(days_visited, 0) AS days_visited,
COALESCE(days_visited, 0) >= :tl_requires_days_visited AS visits_criteria_met,
COALESCE(topic_reply_count, 0) AS topic_reply_count,
COALESCE(topic_reply_count, 0) >= :tl_requires_topics_replied_to AS replies_criteria_met,
COALESCE(topic_view_count, 0) AS topic_view_count,
COALESCE(topic_view_count, 0) >= (SELECT * FROM min_topics_viewed) AS topic_views_criteria_met,
COALESCE(posts_read, 0) AS posts_read,
COALESCE(posts_read, 0) >= (SELECT * FROM min_posts_read) AS posts_read_criteria_met,
COALESCE(num_flagged_posts, 0) AS num_flagged_posts,
COALESCE(num_flagged_posts, 0) <= :tl_requires_max_flagged AS flagged_post_criteria_met,
COALESCE(num_flagged_by_users, 0) AS num_flagged_by_users,
COALESCE(num_flagged_by_users, 0) <= :tl_requires_max_flagged AS flagged_by_users_criteria_met,
COALESCE(topics_viewed_all_time, 0) AS topics_viewed_all_time,
COALESCE(topics_viewed_all_time, 0) >= :tl_requires_topics_viewed_all_time AS all_time_topic_views_criteria_met,
COALESCE(posts_read_all_time, 0) AS posts_read_all_time,
COALESCE(posts_read_all_time, 0) >= :tl_requires_posts_read_all_time AS posts_read_all_time_criteria_met,
COALESCE(num_likes_given, 0) AS num_likes_given,
COALESCE(num_likes_given, 0) >= :tl_requires_likes_given AS likes_given_criteria_met,
COALESCE(num_likes_received, 0) AS num_likes_received,
COALESCE(num_likes_received, 0) >= :tl_requires_likes_received AS likes_received_criteria_met,
COALESCE(num_likes_received_users, 0) AS num_likes_received_users,
COALESCE(num_likes_received_users, 0) >= :tl_requires_likes_received::float / 4.0 AS likes_received_users_criteria_met,
COALESCE(num_likes_received_days, 0) AS num_likes_received_days,
COALESCE(num_likes_received_days, 0) >= (SELECT * FROM min_likes_received_days) AS likes_received_days_criteria_met
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 visits_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE replies_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE topic_views_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE posts_read_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE flagged_post_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE flagged_by_users_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE all_time_topic_views_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE posts_read_all_time_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE likes_given_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE likes_received_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE likes_received_users_criteria_met END
AND CASE WHEN :show_all_results THEN true ELSE likes_received_days_criteria_met END
ORDER BY days_visited DESC